large union query

Hi there
I have a large union query that produces a "timeout expired" error. I've
changed the timeout settings to 600 seconds but I still get the message.
My query and underlying database are characterized as follows:
a) two fairly large database tables (1.5m and 0.7m rows respectively)
b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
c) a couple of "LIKE '%this%'" clauses in each SELECT statement
The query should return around 7-8000 rows.
Are there any guidelines, either as to the maximum feasible size of such a
query, or as to an alternative method to achieve the same thing?
Best regards
Loane
Hi
LIKE '%this%'" results in a table scan as it can not use an index. Is there
any other column that is not using a like that could reduce the number of
rows SQL Server would need to process?
Scanning 2.2m rows can take long. How long does it take in query analyzer?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Loane Sharp" <look_sharp@.hotmail.com> wrote in message
news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Hi there
> I have a large union query that produces a "timeout expired" error. I've
> changed the timeout settings to 600 seconds but I still get the message.
> My query and underlying database are characterized as follows:
> a) two fairly large database tables (1.5m and 0.7m rows respectively)
> b) a "SELECT DISTINCT" query for each table joined by a "UNION" statement
> c) a couple of "LIKE '%this%'" clauses in each SELECT statement
> The query should return around 7-8000 rows.
> Are there any guidelines, either as to the maximum feasible size of such a
> query, or as to an alternative method to achieve the same thing?
> Best regards
> Loane
>
|||Hi Mike
Thanks for this ... I'm pretty much self-taught on SQL Server and generally
prefer to use T-SQL from the SQL pane, which no doubt this causes all sorts
of problems. Moreover, I'm not a DBA (actually an economist/statistician
type) so my use of SQL Server is probably not the one for which it was
designed. But it is a bit worrying to me that changing the timeout settings
to 10 minutes (Tools>Options>Advanced...) doesn't make an impact on a large
or complex query, which still returns a timeout error after 4 or 5 seconds.
I haven't used Query Analyser much and will check this out.
Best regards
Loane
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23SHhklALFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi
> LIKE '%this%'" results in a table scan as it can not use an index. Is
> there
> any other column that is not using a like that could reduce the number of
> rows SQL Server would need to process?
> Scanning 2.2m rows can take long. How long does it take in query analyzer?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Loane Sharp" <look_sharp@.hotmail.com> wrote in message
> news:eMVuDw$KFHA.2716@.TK2MSFTNGP15.phx.gbl...
>