The issue that it got slow after the OR's were added suggests to me that the
OR's are preventing the usage of indexes.
SELECT acolumn, bcolumn, ccolumn
FROM tablename
WHERE
(acolumn = 'boo' AND bcolumn = 'yikes') OR
(bcolumn = 'haha' AND ccolumn = 'clown')
can run a lot slower then:
SELECT acolumn, bcolumn, ccolumn
FROM tablename
WHERE (acolumn = 'boo' AND bcolumn = 'yikes')
UNION
SELECT acolumn, bcolumn, ccolumn
FROM tablename
WHERE (bcolumn = 'haha' AND ccolumn = 'clown')
Despite the fact the union in theory scans the table twice, if the scan can
use an index 2 index scans is still MUCH faster then one table traverse.
NOTE: You may need a DISTINCT clause to purge duplicates for records that
satisfy BOTH of the UNION'd queries, some engines (correctly) imply the
DISTINCT, some do not. I wouldn't trust MSSQL to be smart enough to
eliminate the duplicates rows, which leaves you in a bad place if you want
to allow duplicate rows of VALUES but not duplicate TUPLES.
Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
[EMAIL PROTECTED]
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of A. Karl Zarudny
> Sent: Wednesday, June 12, 2002 10:02 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [CFTALKTor] searching 65 million records with MSSQL
>
>
> Bob and Terry,
>
> Thanks for the quick reply. "Crapping-out" was actually my
> technical term
> :-) At 12:30am I just couldn't think straight. Apparently the
> query was
> timing-out. The server is a Dell P450-ish running NT4 and I
> would assume
> MSSQL7. As, mssql would do fine if searching on just one or
> two columns with
> no OR. As soon as the first OR was added to the WHERE, things
> began grinding
> to a halt and on additional ORs, the time-outs began.
>
> The idea of breaking everything into chunks was suggested by
> another person.
> I figured mssql shouldn't need to do that but since I have yet to know
> "everything" I figured I'd ask :-)
>
> I'll try to get more detailed info, as well as the sql
> statement being used,
> within a day or two and post it to the list.
>
> Thanks again for the insight.
>
> Karl
>
> > From: [EMAIL PROTECTED]
> > Reply-To: [EMAIL PROTECTED]
> > Date: Wed, 12 Jun 2002 07:16:18 -0400
> > To: <[EMAIL PROTECTED]>
> > Subject: RE: [CFTALKTor] searching 65 million records with MSSQL
> >
> > If your table is that big, don't use MS-SQL. If he didn't
> like MS products,
> > perhaps he would know a more concise term then "crapping out".
> >
> > Searching in "chunks" of records will probably cost you
> MORE cpu time, not
> > less.
> >
> > If there is no index on the fields in question, apply
> relevant indexes to
> > make the select faster.
> >
> > If there are indexes, the "OR" statement may be preventing
> the usage of an
> > index. Try using a UNION across 2 separate queries.
> >
> > Terry Fielder
> > Network Engineer
> > Great Gulf Homes / Ashton Woods Homes
> > [EMAIL PROTECTED]
> >
> >> -----Original Message-----
> >> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On
> >> Behalf Of A. Karl Zarudny
> >> Sent: Wednesday, June 12, 2002 12:30 AM
> >> To: [EMAIL PROTECTED]
> >> Subject: [CFTALKTor] searching 65 million records with MSSQL
> >>
> >>
> >> Hi everybody.
> >>
> >> Hopefully one of you can provide some insight to this one. I
> >> recently met
> >> someone who said they were having trouble with MSSQL
> >> crapping-out (that's a
> >> technical term) when querying a 65 million record db. Their
> >> SQL statement
> >> which they were apparently trying to run from Query Analyser,
> >> was something
> >> like....
> >>
> >> select
> >> acolumn,
> >> bcolumn,
> >> ccolumn
> >> from
> >> tablename
> >> where
> >> (acolumn = 'boo' AND bcolumn = 'yikes') OR
> >> (bcolumn = 'haha' AND ccolumn = 'clown')
> >>
> >>
> >> I'll try to get the exact query they were trying to execute.
> >> However any
> >> thoughts at this point? Is 65 million records more than MSSQL
> >> can handle?
> >> Any suggested workarounds?.... perhaps search in chunks of
> records as
> >> opposed to all of them?
> >>
> >> Thanks,
> >> Karl
> >>
> >> -
> >> You are subscribed to the CFUGToronto CFTALK ListSRV.
> >> This message has been posted by: "A. Karl Zarudny"
> >> <[EMAIL PROTECTED]>
> >> To Unsubscribe, Please Visit and Login to
http://www.CFUGToronto.org/
>> Manager: Kevin Towes ([EMAIL PROTECTED])
> http://www.CFUGToronto.org/
> This System has been donated by Infopreneur, Inc.
> (http://www.infopreneur.net)
>
> -
> You are subscribed to the CFUGToronto CFTALK ListSRV.
> This message has been posted by: [EMAIL PROTECTED]
> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> Manager: Kevin Towes ([EMAIL PROTECTED])
http://www.CFUGToronto.org/
> This System has been donated by Infopreneur, Inc.
> (http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "A. Karl Zarudny"
<[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: [EMAIL PROTECTED]
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
