Accessing Large data in database

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

Posted 12 Feb '13 - 4:31

djj5521.8K

Comments

Espen Harlinn - 12 Feb '13 - 11:07

Looks reasonable :-D

Member 9330747 - 14 Feb '13 - 5:10

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:58

Then look into http://msdn.microsoft.com/en-us/library/bb510489.aspx[^] But you probably still don't need the temp table.

djj55 - 14 Feb '13 - 6:29

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

Member 9330747 - 14 Feb '13 - 10:57

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.

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

Posted 15 Feb '13 - 5:31

sbromanv589

PIEBALDconsult - 12 Feb '13 - 9:40

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:20

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 - 12 Feb '13 - 10:15

what version of sql server you are using?

I am using SQL Server 2008

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

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:58

I dont have direct access of DB. I've only select permission.

José Amílcar Ferreira Casimiro - 15 Feb '13 - 13:27

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.

0	Sergey Alexandrovich Kryukov	508
1	Arun Vasu	275
2	Maciej Los	238
3	OriginalGriff	215
4	Mahesh Bailwal	200

0	Sergey Alexandrovich Kryukov	9,660
1	OriginalGriff	7,329
2	CPallini	3,968
3	Rohan Leuva	3,339
4	Maciej Los	2,851

Accessing Large data in database

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

		9,889,766 members (37,108 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

Interested
Ignored

Accessing Large data in database

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us