Well you can only use the table datatype on SQL 2000. But you simply use it much the same as any variable you can declare or SET..... think of it as a virtual table in memory...one step up from a temporary table.
The chances are that you have Auto Statistics switched on, but there are certain rules in which it uses for the autmatic management of them : If the number of rows in a table is greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made. If the number of rows in the table is greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified. As you can tell this can lead to bad performance over time. If you do a select on sysindexes table of the database in question, and look at the "rowmodctr" column, it will show you what the count is, from this number, you can estimate when the next automatic update of statistics will occur and decide if you want to manually run them or not. To manually update them, you should use STATISTICS or sp_updatestats. -----Original Message----- From: Snake Hollywood [mailto:[EMAIL PROTECTED] Sent: 20 October 2003 13:55 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] SQL DB speed 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] -- ** 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]
