ADO Recordset time out during query..PLEASE HELP

PLEASE HELP!
I have a large query that takes about a minute to run from the SQL Query
Analyzer. Now when I run the query from VB6 it craps out 30 seconds into it.
Is there a way around this without manipulating my query. I'm attaching the
query maybe you'll see something I can adjust i'm not a SQL guy so my query
may come off as crude but it works.... sort of!
"SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
(Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
NewDateString + "', 102)) AND (Master.ProjectNumber >10)
AND (Not (Master.ProjectNumber=5682))
AND (Not (Master.ProjectNumber=4520))
AND (Not (Master.ProjectNumber=5535))
AND (Not (Master.ProjectNumber=6042))
AND (Not (Master.ProjectNumber=5867))
AND (Master.VolumeID Is Null)
ORDER BY Master.recordnumber;"
Thanks
Rob
IT guy!
at very quick glance,
is there a nonclustered index on ProjectNumber column?
is there a clustered index on the recordnumber column?
you might need to check Index Tunning Wizard for suggestions of possible
indexes
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!
|||In addition to tuning the sql statement and making sure that you have
appropriate indexes in place, have you tried increasing the .CommandTimeout
(within your VB code)?
Keith
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!