I actually made the requested changed to my SQL command but there was no discernable difference. I really wasn't expecting any.
Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 9473 3944 Fax : +618 9473 3982 E-Mail : [email protected] The contents of this e-mail transmission outside of the WAGHS network are intended solely for the named recipient's), may be confidential, and may be privileged or otherwise protected from disclosure in the public interest. The use, reproduction, disclosure or distribution of the contents of this e-mail transmission by any person other than the named recipient(s) is prohibited. If you are not a named recipient please notify the sender immediately. From: [email protected] [mailto:[email protected]] On Behalf Of noonie Sent: Wednesday, 23 June 2010 11:14 AM To: ozDotNet Subject: Re: Embedding SQL index hints into SQL commands for SQL SERVER. Peter, If the DBA has created that index on the bit column and if the normal process of your store-and-forward results in a very few records matching your criteria then the index may actually help your query times. <http://msmvps.com/blogs/greglow/archive/2004/12/20/26432.aspx> As to whether this is the "right" resolution for the problem or merely a "political" one may be a matter for argument ;-) <fiction> Boss: "How's that database problem going?" Snarky DBA: "I sent him the query that would fix his poorly performing program. (bloody application developers shouldn't be alowed to write TSQL!)" </fiction> -- Regards, noonie On 23 June 2010 11:40, Maddin, Peter <[email protected]<mailto:[email protected]>> wrote: Thanks It is not the inserting of records into the database that is a problem. This is in fact lightening fast. The problem is obtaining a record set of data to be forwarded using a select statement. The table uses bit flags to determine which records need to be forwarded as apposed from those that have been already forwarded. For several years, the select statement has always returned its result set with 30 seconds. There are a two sets bit flags which are used to determine where the data is to be forwarded to. One query is working fine (for the present), the other is timing out. I could extend the timeout but eventually it will again timeout as the database grows in size. The database contains many historical records that are literally years old. There is no need to these to be kept and these can be deleted. The select statement returns a Text blob field which has data that can be several kilobytes in size. I have removed this from the select statement and will be selecting this for each row of the select statement returned. Regards Peter Maddin Applications Development Officer PathWest Laboratory Medicine WA Phone : +618 9473 3944 Fax : +618 9473 3982 E-Mail : [email protected]<mailto:[email protected]>
