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]
