Enlighten me on the statistics and table datatype I have indexes setup, but it's still taking a very long time to run queries
> -----Original Message----- > From: Robertson-Ravo, Neil (RX) > [mailto:[EMAIL PROTECTED] > Sent: 20 October 2003 13:41 > To: '[EMAIL PROTECTED]' > Subject: RE: [ cf-dev ] SQL DB speed > > > Hey, > > There are lots and lots of things you can do. First off, > what version of SQL Server is it? As the Query Optimizer in > the versions are very > different, especially from 7 to 2000. You can even speed up > a SELECT * > from a table in SQL Server by using Hints. SQL Server uses > the Q.O. to > "determine" how and what the best way to perform a query is, > in most cases this is OK, but you can find that by tweaking > your SQL a little you can find a better way that the Q.O. did > not determine. > > Primary Keys are obivously not ideal as VARCHAR's as lookups will take > slightly longer than necessary. You should ensure your > indexes are up to > date and CLUSTERED on fields which are searched on (either by > SQL or the > front-end) frequently. > > You should also update your SQL Servers STATISTICS where > possible : this is especially true with SQL 7 as its not set > to On by default. Updating them and keeping them live is one > of the best ways to keep data performance sweet. > > Limit the amount of Triggers you use and also the amount of > Views are there > are processor intensive. Cursors can also be a bitch on CPU > time so, if > you can, such as in SQL2K, use the Table datatype as this > will speed up complex searches 10x fold. > > Let me know if you need any more info. > > > > -----Original Message----- > From: Snake Hollywood [mailto:[EMAIL PROTECTED] > Sent: 20 October 2003 13:29 > To: [EMAIL PROTECTED] > Subject: [ cf-dev ] SQL DB speed > > > We have a huge 11GB SQL Server database, pretty much all data > is varchar, even the primary key/indexes Any tips on > improving performance and speeding up searches. > > Russ > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
