I have a more or less complex query in my database which returns in average about 50 to 100 rows from a table let's say TBL_A containing about 1 million rows (this with left join to another table containg about 10 million records).
The query itself returns the result in about 0.03 seconds which is pretty much nice I think. The amount of data per row is much less than 1KB.
But as soon I add an "ORDER BY TBL_A.ID" the query needs 1.5 seconds; Where TBL_A.ID is the indexed primary key, integer.
I assume the "query compiler" does try to use the index on TBL_A.ID to perform sorting and thats why the whole PLAN becomes inefficient.
Is there a way to tell the SQL Server not to use "TBL_A.ID index" for sorting and therfore force an "in Memory sorting"?
Any hint is appreciated, also if it is MS SQL related, I think I can transform it by myself to Interbase.
A work around of course would be that I stay without ORDER BY in the query and let the client sort the result, but I don't like to do this.
Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL. And unfortunatelly there is nothing like like CTE in Interbase (at least what I know)
Visited your Profile and read "I'm a Delphi-convert", so then I assume you know interbase. I'm on the way to become hopefully a "Borland c++" convert.
I have tried the following code but did not get the accurate output.
WITH CW as (
SELECT --@StartTime STARTDATE
-- DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartTime), 0) FirstDayOfWeek
DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 0) STARTDATE
, DATEADD(WK, DATEDIFF(WK, 0, @StartTime), 6) ENDDATE
SELECT DATEADD(WW, 1, STARTDATE)
, DATEADD(WW, 1, ENDDATE)
WHERE DATEADD(WW, 1, STARTDATE) <= @EndTime )
I have few more doubts please help to resolve it..
EX: Suppose for the same machine(machineID = 50) there are two different process started at different time. First one(process P1) started at 2016-01-04 02:19:45 and ends at 2016-06-08 07:57:37.000. Second one(process P2) started at 2016-10-01 02:19:45 and ends at 2016-12-31 07:57:37.000. So how can we take calendar week between two dates for the same machine? i mean startdate is 2016-01-04 02:19:45 and enddate is 2016-12-31 07:57:37.000 for the machineID = 50.
Its not working as per my requirement. For the first set of dates the output data is repeating.
I mean to say, for the startdate :2016-01-04 02:19:45 and enddate :2016-06-08 07:57:37 the total number of calendar week is 35.so the output is repeating for the same but for second set of dates i am getting proper output.I have used the same code snippet witch you have suggested.
When you create an EF Data Context using DB first it creates data models for you. Those models are in the EDMX. I created the data in a project called DAL. Therefore the data context and the data models are all in that project.
So then I add this project to a solution along side a WPF/MVVM project. To use the data models I then reference the DAL project, which gives me access to the data models.
With EF is this the right way to do this? Directly reference the generated Data Models? Or do you somehow use your own data models? How do the generated Data Models handle INotifyPropertyChanged implementation in the WPF/MVVM project?
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
I am making a custom Sitemap from my database and stuck with following Issue
SELECT term_id FROM wp_term_taxonomy WHERE taxonomy="product-cat" || taxonomy="product-brand"
It returns all term_id from my database through which I gather category slugs from another table to make sitemap URLs. Below is the code and it works fine as you can see in (sitemap output), but unfortunately I am unable to extract next row term_id & it shows always same term_id to me as a result 'Same URL'
var ds = new DataSet();
using (var connection = new SqlConnection("..."))
var da = new SqlDataAdapter("SELECT ...", connection);
using (var connection = new OleDbConnection("..."))
var da = new OleDbDataAdapter("SELECT ...", connection);
Many years ago I experimented with SQL Server returning multiple tables from a single stored procedure. The performance was dramatically slower then return multiple single tables from individual stored procs.
Performance may have changed in recent versions.
Never underestimate the power of human stupidity
I just want to ask, what is your solution on storing encrypted data on database? And also how you do searching on it? For now the subject database server is mysql.
Disk level encryption is not an option. I also research about database level encryption but, MySql TDE is not for me, and also if you migrate to other database server in my understanding you need to re-encrypt all the data.
So my last bet is application level encryption. And I tried to implement it using AES-GCM, but on this I will lose database searching, because encrypting the same data with the same key and id will produce different results. Now I got an idea but, i don't know what would be the risk implementing it. I was thinking blind indexing.
What I am planning to do is to store 2 version of the encrypted data. first the data will be encrypted with aes-gcm, then store another version of it using one-way encryption on this same data will produce the same hash. So the first encryption is reversible but not searchable, while the second one is not reversible but I can do whole word searching.
The obvious drawback of this is speed. But I want also to ask if in security perspective is it feasible? And also how do you do a search query on encrypted database using the partial text of the data?
You do not explain what kind of data it is and what search you want to do...
As for the HASH based search - it will work only if you search for exact data, for instance 'Gilbert Consellado' will produce a constant HASH but the HASH for 'Gilbert' will not be part of it, so you will find that piece of data only if you are looking for 'Gilbert Consellado' exactly as is (include case and space and order)...
If there is a way to index the data, prior encryption, than you can use it (the HASH of the indexes) to search quickly at the DB level...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Last Visit: 31-Dec-99 18:00 Last Update: 23-Jun-21 17:36