SQL Server \ "Code" Speed

[ss7thirty]

When running company software I am throwing a lot of statements at the SQL server. What are some common practices both through code and through solid networking practices that can improve the speed of software like this. If you need some snippets of the connectors and objects I use to execute queries and to connect to the server, I can provide them.

I would like to try to figure out what to do on the network side of things if possible because noone at my company knows much about network administration. It seems to go a lot faster late at night or early in the morning when there are less people running it. This makes perfect sense, however this leads me to believe that I need to make some changes to our network in order to handle more users and statements.

 

[TechGnome]

Use stored procedures.... don't build the SQL in your app and execute it... do as much as you can on the server.

And only pull down the data you actually need. Don't pull down an entire table and all it's column only to use one value out of one row. Just return that one value. And don't grab the entire table to insert a new record.... send the data to the server via parameters and use an INSERT statement.

-tg

 

[cjard]

I'd say this is quite a hard question to answer because it;s so wide. TechGnome has some great advice in there and you should definitely keep to it. The most crucial things are using queries that the database can cache - eeither stored procs or parameterized sqls. Minimise tha maount of data flowing over the network and be very conscious at all times of where your data actually is and where it is flowing.

Beyond this, it would be better if you can ask us specific questions..