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
LoaneHi
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...
>