How to improve speed of query MSDE 2000

Hi All
I am having trouble with query timeouts in a VB6 program using MSDE 2000 (on
local PC)
It only happens when querying between certain dates ( surprisingly it occurs
when the dates are closer together and hence less records exists)
e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
if date range 25/8/05 to 1/9/05 it does?
code.....
sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as myprice
from [idetail] where [inv_num] in (Select inv_num from Invoice where
inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") & "' and '" &
Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code order by prod_code"
rs.Open sql, cn, adOpenKeyset, adLockReadOnly
I have no primary keys or indexes on the idetail table
Any ideas
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am having trouble with query timeouts in a VB6 program using MSDE
> 2000 (on local PC)
> It only happens when querying between certain dates ( surprisingly it
> occurs when the dates are closer together and hence less records
> exists)
> e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
> if date range 25/8/05 to 1/9/05 it does?
> code.....
> sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as
> myprice from [idetail] where [inv_num] in (Select inv_num from
> Invoice where inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") &
> "' and '" & Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code
> order by prod_code"
> rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> I have no primary keys or indexes on the idetail table
>
a proper indexing schema (as long as a proper primary key, which is always
usefull :D) should always help... but this should not be a related
problem... your actual plan should always be a table scan, followed by a
sort operation, but again, should not be related...
do you have actual lock pending on the table during the failing projections?
try executing
EXEC sp_lock
http://msdn.microsoft.com/library/de...la-lz_6cdn.asp
and
EXEC sp_who
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
to determin eventual lock conditions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply