Which is the best way to do it?
-
Problem
DB1
Table1
Table2
Table3
.... TableN
We need to search through to find through all the N tables if there is a value Name1 in the field called Name.
One is working on crusor and another one is working on looping with if -else but the thing I am thinking about is - performance - which is the best way to do it and why?
-
This may work without looping :
select 'table1', * from t1 where name='Name1'
union
select 'table2', * from t2 where name='Name1'
union
select 'tableN', * from tN where name='Name1'
-
the thing is we need to first figure out if the table has a field called Name and then if it has, then find if it does has value Name1 for the field in that table.
I mean not all the tables from Table1-N have field Name, so we need to find out all the tables with Name field and then amongst the resultant tables find the Name1 value ....
Hope you get me... or I will try and get the code, after they kind of finsihed...
-
How about this. Since you know how to do it both ways, why don't you run some tests and let us know how did they go.
Many people will tell you to avoid cursors but I think you have to deal with each case individually based on the performance tests. I always try to avoid cursors and loops in general but sometimes you have no choice.
But as much as I don't like cursors I found that in some cases they perform faster then any other way of looping.
Also the looping could be implemented in different ways. For example you can do a loop when you always select from your permanent table or you can preselect data into the temp table or table variable and then loop thru the temps.
You may get a suggestion to use sp_MSforeachtable but this one internally uses cursors as well...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
I might have misunderstood your post so if you want to find all the tables you could use this and then build on it.
SELECT SO.name
FROM sysobjects SO
INNER JOIN syscolumns SC ON SC.ID = SO.ID
AND SC.name = 'Name'
WHERE SO.xtype = 'U'
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply