sQL Server Data retrieval Problem

[Orochi663]

Hello,

i am getting a hard time in minimizing time for data retrieval, over SQL Server DataBase. My DataBase consist of 2 tables. One of the table has more than 10000 entries and other table with more than 10 million entries. I have used SQLNative Client for connecting to data base and my goal was to find a value from the 1st table and search it out in 2nd table. The result is more than hundered thousand rows. Now the problem is: the time it took for retrieving those rows is much slower approx. 12 minutes. Can this time be cut down. I have used SQLClient connection to make sure it is accessing SQL server on a direct access base.



Also, i am using

SqlClient::SqlDataReader

for reading, getting rows returned by the my query.



Please help me out.

Thanx.



R. T.

[techguru]

Hello,

Here are couple of things I would do ...

SqlClient should work fine ... since I am also using on the tables with more then 20 M

convert your embed sql to stored procedure

check the indexes on both tables on the fields you are using in where clause ....

try join or sub query insted of 2 query process....

some thing like this ...

select * from table 2 where table2.value in ( select table1.value from table1)

since you don't need all the data to show for the user .. I would use limit also depending on where to start and end

if you could give me the sql queries I can help with stored procedure and using sub query or join...

Thanks,

[Orochi663]

HI,

I have used namespace System:: Data:: SqlClient for making and executing the queries (i don't know whether it is SqlNativeClient or not)

and SqlClient:: SqlDataReader for reading the result of the queries.


The first query is simple:

Select * from Table1 where Entry1 = 'EnterytoFind'

used SqlClient:: SqlDataReader to read data into a variable Var.



Then executed Second query as

for(long Val = 0; Val <= 10000; Val++)// for 0-10000 values of Val

{

Select * from Table2 where Entry_A = Var and Entry_C = Val
// Giving Result: populated in a List

}



Now the main thing is Table 2 is indexed on the basis of Entry_A and Entry_C. Database contains no primary key but a composite key based on Entry_A and Entry_C.



I hope this will explain the problem in more clearity.



R.T.

[techguru]

Hello,

You need to convert your sql query to stored procedure and change sql to either join or sub query to give you desired result.. other wise there is no way the performace can be improved dealing with such huge data

You sql adaptor code is fine ... it's just matter of converting to stored proecdure and using sub query or join.. check the indexes als oon those tables Entry_A and Entry_C

[pegas]

techguru is right. There are also other things you can/should do:

1. review your project. "The result of more than hundered thousand rows" is usually not a result, just a midstep. You can go a few more steps on the SQL server (preferably in the stored proc) to process the data and to bring back the ANSWER to the real question asked.

2. Replace the * in "SELECT * " with the list of columns you do really need.

3. Avoid calling the database 1001 times, do it with interval select:
Select <fieldlist -- remember?>
from Table2
where Entry_A = Var
and Entry_C BETWEEN = 1 AND 10000

You will help that query a lot if you put Entry_C column into clustered index

[pegas]

sorry, in 3. I meant BETWEEN 0 AND 10000

[Orochi663]

Hi,

Well, this things help in reducing time effectively,
Now the main point is, Loop was given there just to give an idea that, that SQL query will run approx for that number of times. i have tried implementing it inside a stored procedure, and called that stored procedure from inside C++ (As you might have noticed that it is using C++ "for" loop and "not" standard SQL "WHILE" loop), for this number of times based on the result i am getting. This happen to be my constraint that i have to call this stored procedure from within C++.

Sorry, if i was not able to clear it.

The exact problem statement is that i have to make this loop work for every entry of the table 1, that means a nested loop initiated inside C++ calling 2 stored procedures or 1 Select 1 Stored procedure, etc.
like:




for every entry in table 1
Var = table1.curEntry
Call Stored Procedure with Parameters Var

Stored Procedure:
[-------------------------------------------------------------------------------------------------------------]
[ for Var = 0 to 10000 ]
[ get all rows of Table 2 such that Table2.Entry_A = Var and Table2.Entry_C = Val ]
[-------------------------------------------------------------------------------------------------------------]



I was thinking that if i can make this stored procedure efficient that i can do it efficiently for whole problem, but it seems it will not work here like that.

I also have one more shot to look for, which is using SQL Server Integration Services.
What i have in mind is to have an Integration services with Data flow giving Data inside memory using "DataReader Destination", of the Table 1, and somehow it would be available inside the stored procedure so that it can utilize it.

Regards,
R.T.

[pegas]

loop in a stored procedure (or even calling a stored procedure from a c++ loop) is usually a travesty. Did you missed what techguru said about "using sub query or join.."? If you do not need to do some really sophisticated processing on the table1 record to be able to search the table 2, there is no reason to use any loop instead of join. SQL is inherently set-oriented.

Get some good book (I personally recommend Celko; especially if you have at least some knack for mental gymnastics). Or, post or send me the current version of your stored procedure (with the loop) I might be able to show you how to get rid of looping.