Like on this website, i've found some usefull tricks to speed things up there.
http://www.sql-server-performance.com/ Marc > -----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: "Marc Campeau" <[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)
