Accessing Large data in database

Question

0.00/5 (No votes)

See more:

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 (

SQL

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 3:29am

Member 9330747

Updated 15-Feb-13 2:08am

v2

Add a Solution

Comments

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.

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.

José Amílcar Casimiro 12-Feb-13 10:15am

what version of sql server you are using?

Member 9330747 14-Feb-13 5:10am

I am using SQL Server 2008

José Amílcar Casimiro 14-Feb-13 6:02am

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.

José Amílcar Casimiro 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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Corporal Agarn · Answer 1 · 2013-02-12T04:31:00

Solution 1

Try something like this:

SQL

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

Posted 12-Feb-13 4:31am

Corporal Agarn

Comments

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 ???

PIEBALDconsult 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.

Corporal Agarn 14-Feb-13 6:29am

Would it be possible to port the table to SQL then use it?

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.

sbromanv · Answer 2 · 2013-02-15T05:31:00

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