I have a master table T1 which has 1000 records and a ColRef a key column. There are lots other tables all linked by ColRef.

I have a Stored procedure which takes few hours to process. I want to split this process like creating a view from T1 select records like 1 to 250, 251 to 500, 501 to 750, > 750. and run this from a .Net program. I cant make changes to any of the SQL components, including procedure or views. instead if pass a hint from my 4 instances of program to run different sections of data. Looks like an impossible situation, but could someone come across this kind of situation.

The whole process takes 8 hours, if I can split into 4 instance and run in 4 different servers(but one database) would take around 2 hours theoretically.

  • 1
    Look at the query plan generated by DBMS. Observe presence/absence of parallel operations, and relative CPU cost to IO cost - the likelyhood is DBMS is already doing it as parallel as is reasonable – Caleth May 24 '16 at 11:18
  • Get faster servers. – JeffO May 24 '16 at 14:38
  • 1
    The not changing the select query is unreasonable. 1000 records is nothing, even if you joined to a bunch of other tables. You are probably are either lacking indicies or are joining in a weird way (say subqueries that execute for each record). Fixing these problems is the real way to fix this horrendous performance. – Mike Jun 2 '16 at 1:45

By that same argument, if you have 1,000 servers (and one database) you'll finish processing in (8 hours * 3600 seconds/hour / 1000 records = ) 28.8 seconds.

That's clearly wrong. Table locks and I/O will become predominant long before all 1,000 servers start working. I would expect similar problems with 4 servers.

A better approach is to do some careful timing on your stored procedure. Check index usage and check the generated query plans as @Caleth suggested.

Once you know exactly where the bottleneck is, you will be able to rewrite the (probably small) section of code that's causing the problem. You might be lucky and discover that you're missing an index.

Remember, the first rule of optimization is to measure the code so you know you're optimizing the part that matters.

Brent Ozar has some free tools that may help.

| improve this answer | |
  • All i am doing is various selects on different tables and insert in another database. the database is more likely static – KarTk May 24 '16 at 13:52

I use partitioned views (I don't have Enterprise so I can't partition tables). They are perfect for your type of criteria.

See https://sqlsunday.com/2014/08/31/partitioned-views/

| improve this answer | |
  • Thanks for all your comments. 1000 records as I said is an example and my tables have few million records and the process is more sequential, like finish step1 then 2 then 3 and quite few steps. hence I prefer to do a parallel processing on multiple instance. Partitioned views are a good suggestion. will try that and let you know. Thanks All. – KarTk Jun 6 '16 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.