Posted in ColdFusion | Posted on 02-23-2006 | 1,156 views
A reader asks:
Hiya Ray -
Is there any guidelines as to how long a query should take to process? For example, I have a category column that I have return just 10 rows, but my entire database has 60,000+ records and it takes about 1100ms. Is this bad?
It depends. (Tired of me saying that yet? ;) I'd always be wary of saying, "A query should only take X seconds," but I can say 1100 ms is probably way too much. I am not a good DBA. It is something I'm working on improving. But I'd definitely recommend looking into your SQL and trying to figure out why it is taking so long. If you want (and if you still read this blog after I took so long to respond), you can post your code and my readers and I can help debug it.
So what would I use as a general suggestion for the maximum amount of time a query should take? I'd say anything over 50ms should be cause for alarm. (Well, not alarm, but I'd check it out.)
By the way - Starfish can help you check how long your queries are taking.
Also keep in mind that fewer records does not necessarily mean faster queries. If your queries are complex, doing a lot of complex computations or even if your DB is not indexed approprately, the milliseconds can pile up.
I've found that simple queries take 16 or 31 milliseconds to run. Longer obviously for more complex queries or those with bigger result sets.
Here is a query to a 15+ column wide Oracle table with 80k rows. Execution time of 31ms, 30 rows returned:
select * from hremp where position in ('01g0834','013r827','019m898','0a10569','020b009')
or SPV_POS in ('01g0834','013r827','019m898','0a10569','020b009')
order by spv_pos, position
Here is one I didn't write that takes longer than I think it should to run (Time=453ms, Records=2348, tables have 10k & 13k+ records in them):
SELECT DISTINCT P.Part_ID, P.Part_Name, P.Active, AsmtCount.Counter
FROM Participants P LEFT OUTER JOIN
Part_Asmt_Levels PAL ON P.Part_ID = PAL.Part_ID LEFT OUTER JOIN
(SELECT Part_ID, COUNT(DISTINCT Assessment_ID) AS Counter
FROM Part_Asmt_Levels
WHERE Level_ID = 0
GROUP BY Part_ID) AsmtCount ON
P.Part_ID = AsmtCount.Part_ID
WHERE PAL.Assessment_ID = 4
AND PAL.Level_ID = 0
ORDER BY P.Part_Name, P.Part_ID
hit CTRL + K and look at the execution plan
index seek --good
index scan -- not as good
table scan --bad
So you want to avoid table scans by using sargable arguments
also you want to minimize IO by listing only the columns that you need instead of SELECT * FROM....
take a look here to see what else you can do(http://sqlservercode.blogspot.com/2005/12/sql-quer...)
Denis
1) If you are joining 2 files, make sure that whatever field(s) you are joining on are indexed in both files. Nothing like a non-indexed join to slow down your day
2) If you are doing 'alike' queries over and over, consider creating a view. A view is like a SQL query overtop of a table(s) that you can run queries off of! Whenever you can let your DB server do the hard work, it will be faster =) Views are really easy too, check a simple example out here: http://www.faqs.org/docs/ppbook/r25758.htm
3) Order your WHERE clause so that the major limiting factors come first. For example, if you have a table containing orders for the past 10 years, and the 1st thing in your where clause is WHERE Origin = 'MI', you are first finding any order from MI for the entire life of your table! Put date limiters 1st in your WHERE clause, and it should filter down your results quicker (provided you have an index on date!) In this same thought, put indexed fields 1st in your WHERE clause as well, and if all else fails, consider creating either a new index or an indexed view =)
Hope these help!
Mongo like index seek.
In addition to the comments made above about the "slow" query listed, left outer joins are considerably more expensive than inner joins. The two outer joins in that query could be a big contributing factor to the speed.
SELECT title, pdate, category
FROM articles
WHERE pdate >= '#DateFormat(dateAdd('d','-12',liveDate),'yyyy-mm-dd')#'
AND (category = '#a[x]#')
ORDER BY pdate DESC
I've got an array of six categories that I loop over. I run a query based on the current array item (news, sports, etc) that equals the liveDate - the MAX date in the DB - and shoot them into a structure. My liveDate query only runs once.
But if a query comes back empty for that array item, I run the above query to locate the most recent articles for that category going back 12 days. Sometimes it'll be a week or more without an article appearing for that category. It's totally random.
Please be gentle, my kevlar is thin...
SELECT title, pdate, category
FROM articles
WHERE pdate >= '#DateFormat(dateAdd('d','-12',liveDate),'yyyy-mm-dd')#'
AND category in ('#myList#')
ORDER BY pdate DESC
This won't show you a noticeable performance increase on a single query, but it's a great habit to get into and will help if you have a high volume app that does the same query over and over again.
Some other random performance-enhancing tactics:
1) Indexes are your friend. In my experience most web apps perform far more selects than inserts or updates, so liberal use of indexes is a good rule of thumb.
2) Properly normalize your database! This reduces redundant data, which reduces disc I/O operations, which increases performance.
3) Learn how to use a query analyzer tool to see the execution plan of a given query. Use the plan to determine what indexes you might create that will speed up the query.
if a query comes back empty for that array item, and running another query to locate the most recent articles for that category going back 12 days...
It looks like you want to use the TOP 1 limitation instead.
SELECT TOP 1
title, pdate, category
FROM articles
WHERE pdate >= '#DateFormat(dateAdd('d','-12',liveDate),'yyyy-mm-dd')#'
AND (category = '#a[x]#')
ORDER BY pdate DESC
This will always give you the most recent article.
I agree with Todd also: If you can combine your 6 queries into 1 with category in ('#myList#'), that would be much better.
I have a simple philosophy, if the tables have too many records or too complex joins then it becomes a view. Then i am only bringing across the exact number of records i need...
The key is how much data you want, and then what is the best method for bringing it to the coldfusion page you need it for.
Other than having cfdocumentation bookmarked, i also have the ms sql 2000 books online bookmarked for easy reference.
Really helps to easily lookup any t-sql or any sql matter..
http://doc.ddart.net/mssql/sql2000/html/
sudo-name: I posted a question similar to yours on Experts-Exchange.com
http://www.experts-exchange.com/Databases/Microsof...
and they came up with a solution like this:
SELECT title, Max(pdate), category
FROM articles
WHERE pdate >= '#DateFormat(dateAdd('d','-12',liveDate),'yyyy-mm-dd')#'
Simple!
But for that dateformat query, i'd be tending to convert that into a sql function...just a personal preference...
http://rickosborne.org/blog/?p=37
HTH
FROM nede t1, nede t2
WHERE (
t1.id < t2.id
)
AND NOT
EXISTS (
SELECT *
FROM nede t3
WHERE (
t3.no6 =1
AND t3.id
BETWEEN t1.id
AND t2.id
)
OR (
t3.id = t1.id -1
AND t3.no6 =0
)
OR (
t3.id = t2.id +1
AND t3.no6 =0
)
)
ORDER BY t2.id - t1.id DESC
LIMIT 1
/*********/
There is 300 records, and only 1 and 0 in column no6.
Can you please give me some info for why it take so long.
I am aware that it is an unbroken series of values that all are equal. It take about 20 sec to execute the query.
Hope for quick responds.
Best regards Kjell
[Add Comment] [Subscribe to Comments]