4

I'm developing an application which queries data from the host, and then inserts them into an SQL database. My application runs in random times on our PC's ( we have 600-700 PCs ) from a server location, so my application isn't located at all PC. It is only located at the server. So it is possible that sometimes it runs on 50 PCs.

I keep getting two types of SQL error messages. The first is time out, and the other is SSL security error. Right now my application executes about 5-10 SQL commands. So i'm thinking to rewrite my code to call stored procedures which could reduce the number of SQL calls. The only question is that is it worth it? I mean of course, it has its advantages, because if i have to change something then it is enough to change the stored procedure, and i don't have to recompile my application. But won't that cause trouble for the SQL server? I mean isn't that a problem when the same stored procedure will be executed 50 times at the same time?

So what way is better? Using stored procedures, or using SQL commands in my code?

Thanks!

kampi
  • 2,362
  • 12
  • 52
  • 91
  • 3
    If I were you, I'd first understand the exact causes of the timeouts and the SSL errors, before rewriting anything. – NPE Jan 05 '12 at 16:13
  • http://stackoverflow.com/a/15277/916657 is a good read. – Niklas B. Jan 05 '12 at 16:15
  • @aix: unfortunetly i don't have any idee why i keep getting these errors. It doesn't occur on the same PCs. Please have a look, maybe you have an idea : http://stackoverflow.com/questions/8709002/why-do-i-get-hyt00-and-08001-sql-errors – kampi Jan 05 '12 at 16:20

1 Answers1

4

If you often call several identical SQL statements in a row, the answer is clear-cut: use SPs to reduce the number of round trips. The load on your SQL server is not going to change, but the network latency will go down due to reduced number of round-trips. As an added bonus, your system architecture may become easier to understand and maintain, because the complex SQL logic would be encapsulated in your data access layer.

This may not have anything to do with your SSL errors, but the situation with timeouts has a decent chance of improving.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • also stored procedures are compiled while textual commands are interpreted..much faster always use sprocs. – bebonham Jan 05 '12 at 16:18
  • @bebonham Modern RDBMS engines have alleviated this issue significantly by caching execution plans, so multiple SQL statements would not kill your performance the way they did 15..18 years ago. But as far as saving some CPU cycles goes, this is a very good observation. – Sergey Kalinichenko Jan 05 '12 at 16:28