Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
Hi,
 
I've 2 tables in database name Tab1 & Tab2.
Tab1 consists of following columns.
Col1,Col2,Col3,Col4,.....
 
Tab2 consists of following columns.
Col2,Col5,Col6,Col7,.....
 
and a datatable named tempTab.
tempTab consists of following columns.
Col1,Col4,Col6,Col7
 
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
Edited 15-Feb-13 3:08am
v2
Comments
PIEBALDconsult at 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.
Member 9330747 at 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.
José Amílcar Ferreira Casimiro at 12-Feb-13 10:15am
   
what version of sql server you are using?
Member 9330747 at 14-Feb-13 5:10am
   
I am using SQL Server 2008
   
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 at 14-Feb-13 10:58am
   
I dont have direct access of DB. I've only select permission.
José Amílcar Ferreira Casimiro at 15-Feb-13 13:27pm
   
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
good
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 
  Permalink  
Comments
Espen Harlinn at 12-Feb-13 11:07am
   
Looks reasonable :-D
Member 9330747 at 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 ???
PIEBALDconsult at 14-Feb-13 8:58am
   
Then look into http://msdn.microsoft.com/en-us/library/bb510489.aspx[^]
 
But you probably still don't need the temp table.
djj55 at 14-Feb-13 6:29am
   
Would it be possible to port the table to SQL then use it?
Member 9330747 at 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
good
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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 325
1 DamithSL 300
2 Sergey Alexandrovich Kryukov 289
3 CPallini 235
4 Maciej Los 215
0 OriginalGriff 5,455
1 DamithSL 4,422
2 Maciej Los 3,860
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,010


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 15 Feb 2013
Copyright © CodeProject, 1999-2014
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