|
data base unlimited |
|||||
|
|||||
|
Home Up ADO Access VB 6 Visual Basic. NET ASP.NET Exchange Server
Autonumbering SQL Problems Correlation Temp tables Denormalisation Semi-dynamic UK Post Codes SQL7 SP2
|
SQL ServerSQL Server 2000Microsoft SQL Server 2000 is the most prevalent version of Microsoft SQL Server, and has enhanced performance, analysis and management tools when compared with SQL Server 7. Most of our customers use this version and now have solid performance history. SQL Server 2005 (Yukon) supports both 32 and 64-bit systems and the .NET framework. Service Pack 4a for SQL Server 2000 was released January 2005 Client side XML production from SQL Server rowsets is possible with the latest XML Services for SQL 2000. This means that XML documents can be produced from SQL7 through SQL2005. This cam be used as a replacement for sp_makewebtask utility. This is particularly apposite when:
A typical application might be inter-company pricing data. Quick mention of some less obvious but elegant features in SQL 2000:
This means you can now present data based on views in updateable grids without having to write complex front-end code. Under SQL 2000, there is an extra option when creating a VIEW. Normally, whenever you created a view, it was the metadata about the underlying tables that got passed back to OLEDB/ODBC. However, when you create a view WITH VIEW_METADATA, the entire view becomes updatable (if you have an INSERT or INSTEAD OF trigger), or will conform to normal updatability rules. Getting the best out of MS SQL ServerIt is worthwhile applying the 90/10 rule in SQL (90% gain, 10% pain), whatever the SQL vendor or operating platform: where do I get the most return for the least effort? Indices, maintenance and stored procedures To reduce a query from 30 minutes to 3 seconds requires some specialist knowledge and a larger measure of common sense. We recently achieved this, but 90% of the gain resulted from the three key elements: IndexesIndexes must be used judiciously. Indexing the wrong columns in any optimising compiler degrades performance substantially since any index always has a downside -- creation and maintenance overhead. Very often the compiler is forced to use a page by page read method while building indices on the fly -- less efficient than having the index structures to hand. MaintenanceIndices depend on maintenance for efficiency. The optimising compiler often uses predictive analysis (distribution statistics) to determine whether it is worth while using indices or not. If the compiler makes the wrong decision, it is often because no maintenance has been done on statistics or indices. (If yours is not a 24 hour operation, can the indices be rebuilt overnight? And if it is, can your indices be split so that individual indices can be rebuilt at low peak times?) SQL Server Stored ProceduresA SQL Server procedure that is pre-compiled is faster than an ad-hoc query. But the performance gain with stored procedures is not just in server cycles -- there are more important side-effects. If you are working with an intelligent front end like Access, you may end up retrieving unnecessary data from your server and making a little workstation do all the work that could have been done for it by the server. While a three way join on 5,000 row tables may not give you much gain when converted to stored procedures, a four-way join on even a 35,000 row tables shows massive gains when converted to stored procedures and resolved at the server, not the workstation. create clustered index ... SQL ProblemsNow see our SQL problems brainteasers. Copyright DBU 2000.
|
Location Map
Updates
|
|||