Originally Posted by
NoellaG
Hi,
to speed things up: when working with records on SQL server: SQL has a batch approach, not a row by row and loop approach. This is begging the server to go slow and on a tuning project the first thing I do is look at this and advise the development department to replace these with SQL batch processes. Never update/delete/insert row by row on a SQL database, but use joins and criteria to get the set and do the actions for all records in 1 operation.
To speed up 2: use procedures for parameter queries and not pass-through queries. When using pass-through queries, the server will treat them as ad-hoc queries and build a new query plan every time you execute the query. Using stored procedures the server engine sees it as the same query because the parameter is evaluated inside the procedure, so it can reuse the cached query plan.
To speed up 3: create the correct indexes, as a rule: index all foreign keys, choose wisely your clustered index: best choice is in 99% of the cases your primary key (mostly an int or uniqueidentifier). To look for other indexes: switch on the option "Include actual query plan", or for action queries you don't want to really execute in speed tests: display estimated execution plan. In a lot of cases this will include a hint about missing indexes. With non-clustered indexes you can include the columns you need to fetch, so the server doesn't have to do a lookup in the clustered index.
A warning: if you have set up your database using a full recovery model, be sure to take regular log backups, or the log file will become to big for your harddisk and the server will block.