Hi Eric,

I have a procedure that gets data from permanent tables and stores them in hash tables. Once the data is in hash tables, it does number of calculations using the hash tables.

Now, the issue is, a single instance of this procedure is finished in 20 secs, where as 5 instances of the same procedure simultaneously with different parameters is taking upto 40 secs.

When we divided the code into multiple stored procedures and executed, surprisingly SQL Server is able to scale now. Even 5 instances of this procedure is getting completed in Avg 25 secs ( which is reasonable ).

The number of lines of the original sp is only about 2000 lines.

What that explains is, the CPU is being utilized fully when we break the stored procedures into smaller ones.

Could you please throw some more info on this ? Is there any thumbrule for writing SPs in order to avoid concurrency problems ?

Sorry for being lengthy in my explanation, but I wanted to give you clear picture.

TIA Regards

Sudhakar

PS: My sql server is patched upto SP3.

-- Anonymous, October 05, 2004

Answers

Sudhakar,

From your description, I suspect the 5 stored procedures use horizontal partitioning, and thus reduce data contention.

Horizontal partitioning is a good technique to use. The SQL Server Books Online topic, "Partitioning" in the "Optimizing Database Performance" section describes horizontal partitioning thusly: "Horizontal partitioning segments a table into multiple tables, each containing the same number of columns but fewer rows. For example, a table containing 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a given year. Any queries requiring a specific month's data reference the appropriate table only."

Hope this helps,

Eric

-- Anonymous, October 05, 2004