March 26, 2002 at 7:01 am
My database is huge 5 millions records plus.
I need run a querie on a way to give the results fast, it's taking 17 minutes to give the results to the user.
I'm using sql 2000 and a normal select statement.
Any Ideas ???
thanks
March 26, 2002 at 7:11 am
Check your db for the proper indexes (look at the query's execution plan), make sure you have the statistics to make proper index choices, post you DDL for the table and the query itself and we will make suggestions if we see anything that can help. Also note any indexes we cannot see in the DDL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 8:46 am
5 Million isn't huge. Big, but not huge. Can you follow Antares suggestions and give us some more info? We then might be able to help.
Steve Jones
March 27, 2002 at 7:37 am
well this is my statement:
I don't have any indexes it's already ordered by date:
Select SITEID,SUM(SECONDS/60) as total_minutes from oldbill01 where (CALLID='"&MCALLID&"') and ((convert(datetime,convert(varchar(12),callwhen))) >= '"&date_from&"' and (convert(datetime,convert(varchar(12),callwhen))) <= '"&date_to&"') group by siteid order by siteid.
I get the variables from a Intrabet web page
it's run but slow, very slow.
March 27, 2002 at 7:52 am
From a quick look I think some indexes would help. I suspect you say the table is 'ordered' by date because you are timestamping each insert. However I don't think that will help you since you are filtering on a date-range AND the call id.
It's well worth taking Antares suggestion and looking at the execution plan. I'll bet there's at least on full table scan!
incidentally you might like to try the the BETWEEN operator for your date range rather than the >= <= criteria you have at present
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 27, 2002 at 9:17 am
I'm getting "TIME OUT EXPIRED" when I try to run with Between
March 27, 2002 at 9:33 am
well I'm using the command "BETWEEN" and I'm getting "TIMEOUT EXPIRED" but when I refresh the page it's come on ok. every time I refresh I got it...is that any reason ??
maybe cache ??
March 27, 2002 at 9:42 am
Sorry from my 'Quick Look' I glossed over the CONVERT statements you're using in the date criteria.
Is there a reason why you need to use such a complex structure to compare the from and to dates. I've never had any probs using BETWEEN on two numeric or date values (probably the result of a sheltered life.)
Is CAST not applicable?
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
March 27, 2002 at 9:51 am
not reason, the "BETWEEN" is working fine
just give that TIMEOUT EXPIRED, don't know why yet, If I refresh the page works
March 27, 2002 at 9:51 am
Even if the table is ordered by date it will not help you when querying that column on such a huge table as the query has to read each item.
I suggest add an index for date and make it clustered thus it will stay ordered by date still. Now however when you run the query it should
run thru the index first to find the matches however I am concerned with why you do
((convert(datetime,convert(varchar(12),callwhen)))
as I believe this is unneccssary and will cause an index scan when we really want to aim for a seek.
Try (callwhen BETWEEN firstdateval AND lastdateval) and if the problem is you are trying to do based on dates without time or other format I suggest do it on
the variables you input to match the format of the table. Doing this should generate an index seek.
Now the reason. With a table scan it will run faster as the data has fewer pages to read when scanning the index as opposed to a table scan but since this is the table it
servers better in that it has markers to the columns it can use (hope that makes sense).
However a seek a seek will perform better since it will use the leaf pages to find the data. So for example the index may say date range 1/01/2000 - 12/31/2000 does exist
on page 1 and page 2 has 1/01/2001 - 5/02/2001 and so on and thus the reason why higher number of uniques items help you get even better performance as these leaf pages may point
to smaller ranges and thus leafing thru will help pinpoint what pages to look at for the datas existance, then goto those pages an obtain the data.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 31, 2002 at 7:02 pm
I suggest a clustered index on callwhen column, as well as using between statement and forgetting about the convert functions.
Also a non-clustered index on callid column.
If this doesn't get the query to run fast enough, then look at horizontally partitioning your table, probably based on how often you need to access your older data.
April 1, 2002 at 11:56 pm
Build an index on the column(s) you are qualifing on (columns included in the Where-clause)
quote:
My database is huge 5 millions records plus.I need run a querie on a way to give the results fast, it's taking 17 minutes to give the results to the user.
I'm using sql 2000 and a normal select statement.
Any Ideas ???
thanks
April 4, 2002 at 3:15 am
Really simple, you are extracting the hour from a datetime column then comparing.
If you use convert (or any other op over a column), the index wont work.
So, insted, convert your date_from and date_ to to values that would contain all you required result set.
So the 'where' would be siplified to something like this:
where (CALLID='"&MCALLID&"') and
(callwhen >= '"&date_from&"') and
(callwhen< convert(datetime,'"&date_to&"')+1)
then you'll notice a first difference
April 4, 2002 at 4:34 am
In adition to my previous post, and, if you have no problem with disk space, then you should try (only for this exceptional critical case), one of 2 posible indexes:
(SITEID,CALLID,callwhen,seconds) (plenty space, max speed, no table access)
(SITEID,CALLID,callwhen) (less space still much better perf)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply