Click here to Skip to main content
13,408,987 members (49,316 online)
Rate this:
Please Sign up or sign in to vote.
See more:

I've 2 tables in database name Tab1 & Tab2.
Tab1 consists of following columns.

Tab2 consists of following columns.

and a datatable named tempTab.
tempTab consists of following columns.

There are large amount of data(7 million each) in Tab1 and Tab2, and tempTab consists of few data(say 500). Now i've to check which rows from tempTab exist in Tab1 & Tab2. what I did is that I iterate tempTab(within a for loop) and create connection with Database every time to run a query (
select t1.Col1,t1.Col4,t2.Col7 from Tab1 t1 join Tab2 t2 on t1.Col2=t2.Col2 where t1.Col1='tempTab.Col1' and t1.Col4='tempTab.Col4' and t2.Col6='tempTab.Col6' and t2.Col7='tempTab.Col7'
). Thus i've to hit Database 500 times within that loop(for loop) for iterating. I think, this is bad practice. It took more time also.

Is there any solutions for this ???
Please help as soon as possible.
Thanks in advance.
Posted 12-Feb-13 4:29am
Updated 15-Feb-13 3:08am
PIEBALDconsult 12-Feb-13 9:40am
What are you trying to accomplish?
Please show some sample data and the desired results.
I doubt you even need the temptable.
And please learn to use parameterized queries; they can help with perfromance as well.
what version of sql server you are using?
Member 9330747 14-Feb-13 5:10am
I am using SQL Server 2008
Member 9330747 14-Feb-13 5:20am
Yes I've the tempTab(as DataTable written in C#) with around 500 rows. And this tempTab does not exist in Database.
Can you create a stored procedure that accepts an input parameter with xml datatype and put all the records of tempTab at once into the database (stored procedure).
That way you will have only one iteraction with the database.
Member 9330747 14-Feb-13 10:58am
I dont have direct access of DB. I've only select permission.
This maybe stupid, but you can build the select statements in c# using unions and execute a single query against the database. Does the program logic let you do this ? You can also consider to make async calls to the database reducing the execution time (parallel calls) but the server will get a higher load.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try something like this:
select t1.Col1,t1.Col4,t2.Col7 
from Tab1 t1 
join Tab2 t2 on t1.Col2=t2.Col2 
INNER JOIN tempTab ON t1.Col1 = tempTab.Col1 
    and t1.Col4 = tempTab.Col4 
    and t2.Col6 = tempTab.Col6 
    and t2.Col7 = tempTab.Col7 
Espen Harlinn 12-Feb-13 11:07am
Looks reasonable :-D
Member 9330747 14-Feb-13 5:10am
Hi djj55,
I appreciate your kind suggestion. But problem is that tempTab does not exist in database. This table is a DataTabel (written in c#). So, how could I use this SQL query ???
djj55 14-Feb-13 6:29am
Would it be possible to port the table to SQL then use it?
PIEBALDconsult 14-Feb-13 8:58am
Then look into[^]

But you probably still don't need the temp table.
Member 9330747 14-Feb-13 10:57am
No, it's not possible to port tempTab in DB. I dont have direct access of DB. O Actually I collect data(for tempTab) from file, then manipulate & populate tempTab. Now I've to check each rows wheather they are exist (in Tab1 & Tab2 using SQL joining) or not.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Create a table on DB for represent the tmptable( if you need to multiple process make review add adictional column for store a sessionid unique for process) for make joins with other table, use a bulk insert and call a SP for make the review and after it delete tmptable data. You can use and xml to pass the rows but have to serialize on C# and deserialize on TSQL, in this last for large xml it have a low performace

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.180221.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100