|
I have trouble with the whole idea that you the owner of the database do not control who and when the code is changed. You should be controlling who ans when someone or an application updates the code
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, please help me optimize this query
SELECT DISTINCT CC_STRINGENTEREXIT(ems.passdatetime,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) as DetailEnterExit,
CC_DURATIONENTEREXIT(
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.passdatetime) as DurationInZone
FROM EMSINFO ems
where
trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS')) AND
CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME) <> 0 and
CC_CheckForExit(ems.PASSDATETIME,
(
select ems2.PASSDATETIME from EMSINFO ems2
where ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
, ems.PASSDATETIME)
),
(Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid)
) <> 0
and ems.masterplatenumber = '150444833' and rownum <100 ;
you see that
1-
Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
where emsqi.emsinfoid = ems.emsinfoid
has been repeated multiple time, how do I declare it once and use it as many times as I want.
2-
CC_GETNEXTENTER()
has also been repeated, and in the functions I have a similar Issue, how can I get the value of the function once and use it as many times as I want through the query?
Thanx in advance
|
|
|
|
|
1. You use a join. Something like this: Untested!
SELECT DISTINCT
CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND CC_CheckForExit(ems.PASSDATETIME, (
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
),emsqi.PLATENUMBER) <> 0
AND ems.masterplatenumber = '150444833' and rownum <100 ;
2. Use a CTE, Something like this: Still untested!
WITH bla AS (
SELECT CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND ems.masterplatenumber = '150444833'
)
SELECT DISTINCT
DetailEnterExit,
DurationInZone
WHERE GetNextEnter <> 0
AND CC_CheckForExit(
ems.PASSDATETIME,
(
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = GetNextEnter
)
,emsqi.PLATENUMBER) <> 0
AND rownum <100
;
Now, keep in mind that I don't have a clue what your functions do, so my suggestions is only to show you the methodology, It's up to you to see if it gives the right result.
But you want as much of the filtering in the CTE as possible to keep the amount of data down.
|
|
|
|
|
select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id
(just a sample query, dont ask for its purpose)
I'm quite sure that we need an index on st.city_id to speed things up.
But would an index on cs.city_id make any different?
Because it looks like we have to scan entire Customer table anyway.
|
|
|
|
|
Assuming you are using SQL Server, turn on the Profiler in SSMS and run the query, it will then recommend an index if required.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
khun_panya wrote: But would an index on cs.city_id make any different?
Most probably it will. The query optimizer decides whether to use an index or not depending on various criteria. If the number of rows in the table is very less, it might do a table scan rather than an index seek. But this is purely left to the optimizer to decide.
khun_panya wrote: Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
|
|
|
|
|
Shameel wrote: khun_panya wrote: Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.
I have not checked for real execution plan. But above query do select with "no filter". So it would have to iterate all the rows in table. That's why I doubt that an index helps in this situation.
|
|
|
|
|
There is a good chance that an index would speed things up.
The advice given so far is sound, however.. indexes are a bit of an art and like all arts you have to experiment occasionally.
Two areas that affect indexes are the number of rows and the frequency of updates and inserts.
For this reason you need to understand some of the theory behind indexes and also be willing to experiment with adding indexes to see what happens and learn from that.
I don't have any links but I would suggest giving yourself a good hour to google and read up on indexes as it will stand you in good stead.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Here's[^] a really good link about indexes.
|
|
|
|
|
Hello all,
I ran the trace file and then analysed this data in the Anaylser in SQL Server 2008.
I have a table consisting of 2 million rows, and the analyser recommended 15 indexes. I am fairly new to this company and was not sure how to go about this.
Is there some other way to figure which index would be really required? I have a field which is the Primary Key, a datetime field, a unique identified field.
Thank you!
|
|
|
|
|
I usually try and pick the index that is going to hit the most granular field and create that first, rerun the analysis and repeat until satisfied with the performance. This often reduces the number of indexes actually required to gain significance performance gains.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As Mycroft suggests, create the most granular index first. Indexes tend to be more useful when the distribution of data in the indexed column(s) is high. And create one index at a time and rerun the query to see how much performance improvement you've gained, if any. This way, if you find that the query has actually slowed down (which may happen sometimes), you can always disable or drop the index and proceed with the next one.
|
|
|
|
|
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the if...else in the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.
|
|
|
|
|
I'd agree that it's better to put this in SQL than the application. In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
However, I've seen suggestions that something like this might be slightly more efficient:
UPDATE YourTable SET ... WHERE ID = @ID
If @@ROWCOUNT = 0
INSERT YourTable (...) VALUES (...);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
That's why we have transactions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Just don't count on that to work on any database.
And for that purpose it's better to use MERGE[^]
|
|
|
|
|
IMHO anything that data handling (update, insert, delete, move, aggregate and on) is good to do in SQL - it's very good at it.
However IF isn't exactly in that category - so do update then insert where the where clause contains an addition of @@ROWCOUNT = 0 at the end...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks for the feedback; I will use the ROWCOUNT test.
|
|
|
|
|
I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion.
I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record.
I put it into a tip [^].
While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SQL is very smart - an update of non existing record will end in a single index page read. And that read may be from cached index page if it's a highly used table. So no writing at the first part. And if ROWCOUNT isn't 0 no write in the second part...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
To be honest, I don't think it makes any difference which way it is done. Unless you are working with really big data the time taken is irrelevant these days. I was just offering an alternative, my code generator (written in the 90s) does all this work for me 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You may be right - and your approach is very good one. I read your tip and bookmarked it, as I plan to run some benchmarks on large data with you against rowcount...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: as I plan to run some benchmarks on large data with you against rowcount
I would be interested in the results from an intellectual POV but I suspect the differences will be so minute as to be irrelevant. Look forward to the results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Here it comes...
I got a personal info table - containing id, name, user, password, address, email and phone - with 19,245,718 records. The table has PK and indexes...
Run an insert/update of 108,382 records of which 32,190 where duplicated (same id).
With your method it took an average of 26.77 ms/record.
With the @@ROWCOUNT approach it took an average of 13.07 ms/record.
Both test run on the same machine with local SQL after restart.
The computer is a bit old one (one I can spare) with 4x2.93 CPU and 8GB memory, but I believe that the differences are real...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
The most important advantage of the MERGE statement is that the table is read and processed in one pass only.
So if you insert/update only one record there's no real win. It's when you insert/update from a query you'll get the real performance gain.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
