June 26, 2009 at 7:50 am
Hi All,
I have basic understanding of sql server 2005 programming .I have been given a task of developing entire database from the scratch.I have completed the development of the database with the knowlegde i had and the answers to my queries in this forum.
The application works fine with out any issues and all the functionalities has been achieved.
However, i would like to know, what else i need to do from the development front in order to make database effcient. In other words all things i need to look into to be a good database developers.
Up to know, i have created all the tables with the appriopriates datatypes, constraints; Stored Procedures which contains mainly while loops and inner joins to achieve my business logic preventing usage of cursors.
Kindly suggest me what all area in need to look into to deliver a good databas and work towards good sql server programmer.
June 26, 2009 at 8:38 am
This is quite an open-ended question, the things i would check are;
Read up on normalisation and make sure your database in normalised to at least 3NF
http://en.wikipedia.org/wiki/Database_normalization
that you use the appropriate indexes
that you have any neccessary constraints in place in the tables
check you have all PK-->FK defined, in fact make sure you have a PK on each table.
On the Admin side; check that you have a robust backup/restore strategy in place and that the time between backups is in-line with your business needs.
Perhaps if you could give more details about your application you could get more specific answers.
also using a while loop does not neccesarily solve any problems you had with a cursor, you should look at using set-based approach in your procedures.
June 26, 2009 at 8:48 am
One of the harder concepts to grasp is how SQL server works with Set Based operations, compared to programmatically based processing, which thinks about processing data "Row By Agonizing Row" --Jeff Moden
I'm sure you've done this countless times, where you get a recordset (or DataTable in .NET)
and iterate through each row, doing something, and maybe updating at the end.
if you have any stored procedures with a loop, while loop, or cursor, they are not taking advantage of set based processing; with SQL Server, the amount of time it takes to apply some logic against a subset or an entire set of millions of rows are orders of magnitude faster than any loop construct...
if you can show us any of your procedures that contain a loop, whether a counter or while loop or whatever, we can offer suggestions on how to do it via a set based operation; hopefully with a concrete example, where you are familiar with what the proc was supposed to do, the changes can help you grasp the differences.
here's a simple example:
supposed you wanted to update a Invoice Table, so that the company names were all upper cased, because you noticed some percentage of the data were all CamelCase, or all lower case.
you could make a loop to whip thru each row, or a single statement could do it all for you.
Update SomeInvoiceTable SET CompanyName = UPPER(CompanyName)
now, whether there is 1 row or a million rows, that statement will run very quickly.
a loop construct for a few rows won't have a noticable time lag, but if the table has a million rows, all those 1-3 millisecond updates for each row suddenly add up to a huge total time to execute.
Lowell
June 27, 2009 at 8:29 am
some of the things i might add....
1.have indexes on columns which u knw will be frequently used to query
2.initiate change tracking for your databases and the tables....this will help you to track all the changes which you are sure to make down the line.
3.Have a look at SQL profiler....it help you track the updates you make to the tables
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply