Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have been using a cursor to get my results, but it is taking an extended period of time to return my result set. I am hoping to find a way to QUICKLY get the results returned (if at all possible). Here is my data structure with garbage DDL.

1st problem is each employee has there own table. I understand this is a major design flaw, and we are quickly working to remedy this, but I need an alternative until all elements have been updated. All 3 tables geoffrey, jorge, mitch all have the same structure, and I have attached sample DDL for geoffrey, & jorge and below is a sample query that shows what I need to query from each of the two tables.

Greatly appreciate any and all help on how to achieve this result!

Create Table #ReviewDB
(
  [TableName] varchar(500)
  ,[ServerDBTable] varchar(MAX)
)
Insert Into #ReviewDB VALUES
('geoffrey', 'beans.franks.dbo.geoffrey')
,('jorge', 'smallpox.virus.dbo.jorge')
,('mitch', 'mosquito.insect.dbo.mitch')
Create Table #geoffrey
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #geoffrey VALUES
('manny', 'ramirez', '1111111 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '9995552222', 'sofa', '01/21/2016', '')
,('hi', 'ho', '2323 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '6662229888', 'chair', '02/21/2016', '')
,('ee', 'aa', '4646 aoaswdfrljkasdf ', 'topaz', 'mn', '1111', '3332221919', 'ottoman', '01/21/2016', '')
Create Table #jorge
(
  recordID int IDENTITY(1,1) PRIMARY KEY
  ,firstname varchar(500)
  ,lastname varchar(500)
  ,address varchar(500)
  ,city varchar(500)
  ,state varchar(500)
  ,zip varchar(500)
  ,phone varchar(500)
  ,itemsold varchar(500)
  ,saledate datetime
  ,managerapproved varchar(500)
)
Insert Into #jorge VALUES
('aa', 'bb', '1111111 c street ', 'holt', 'tn', '2222', '1113334444', 'sofa', '01/21/2016', '')
,('cc', 'ddo', '2323 b street ', 'holt', 'tn', '2222', '8889997788', 'chair', '02/21/2016', '')
,('mm', 'rr', '4646 e street ', 'holt', 'tn', '2222', '8889998877', 'ottoman', '03/21/2016', '')
Create Table #managerrevieweddata
(
  ID int IDENTITY(1,1) PRIMARY KEY
  ,recordID int
  ,dbname varchar(500)
  ,managernotes text
  ,regionalmanager varchar(500)
  ,districtmanager varchar(500)
  ,incentiveitem varchar(500)
)
Insert Into #managerrevieweddata VALUES
('1', 'geoffrey', 'Valid sale, remind to offer upsell next time.', 'Regional Manager', 'District Manager', 'No')
,('2', 'jorge', 'Review with salesman', 'Regional Manager', 'District Manager', 'NO')

And here is a query that shows what I want my desired output to be.

I need a way to query each serverdbtable in #ReviewDB and return the below select statement

Declare @saledate datetime
Set @saledate = '03/21/2016'

Select a.firstname, a.lastname, a.address, a.city, 
a.state, a.zip, a.phone,   a.itemsold, a.saledate,
b.dbname, b.managernotes, b.regionalmanager, 
b.districtmanager, b.incentiveitem
FROM #ReviewDB.ServerDBTable a
INNER JOIN #managerrevieweddata b
ON a.recordID = b.recordID
and a.saledate = @saledate
share|improve this question
1  
So, have you tried to write any solution for this? If so, please show what you've attempted. – Max Vernon Mar 21 at 18:15
    
Try SP_MSFOREACHTABLE or dynamic sql. Should be better than cursors. – Vladimir S. Mar 21 at 18:40
    
@VladimirS. - I have never used SP_MSFOREACHTABLE - will check out google now. Need faster than cursor thank you! – Michael Mormon Mar 21 at 18:43
3  
Let's look at the source code for sp_msforeachtable before we start saying it's not a cursor. – Aaron Bertrand Mar 21 at 18:43
    
I might be getting a big jaded in my old age, but we scream not to build databases this way but people still do. Then they asks us to help them with querying these badly designed databases. – datagod Mar 21 at 19:59
up vote 5 down vote accepted

It is unlikely that the cursor you are using is the cause of the delay in returning results. Most likely, the delay is due to running many select statements over possibly a large amount of data. Having said that, I likely wouldn't use the DECLARE CURSOR cur FOR ... construct to solve this issue.

I generally approach this class of problem by generating a single dynamic SQL statement, then execute that statement using either sp_executesql() or EXEC (), as in:

IF COALESCE(OBJECT_ID('dbo.TableList'), 0) <> 0
DROP TABLE dbo.TableList;

CREATE TABLE dbo.TableList
(
    DatabaseName SYSNAME NOT NULL
    , SchemaName SYSNAME NOT NULL
    , TableName SYSNAME NOT NULL
);

INSERT INTO dbo.TableList(DatabaseName, SchemaName, TableName)
VALUES ('Database1', 'Schema1', 'Table1')
    ,  ('Database2', 'Schema2', 'Table2');

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END 
    + 'SELECT ''' + tl.DatabaseName 
        + '.' + tl.SchemaName 
        + '.' + tl.TableName 
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName) 
        + '.' + QUOTENAME(tl.SchemaName) 
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

PRINT (@cmd);
EXEC sp_executesql @cmd;

This constructs statements required from the TableList table, appending each statement into a variable. The variable contents are then executed using the sp_executesql system stored procedure. This methodology can be used for a multitude of problems where you need to generate T-SQL statements based on the results of some query.

The output will be a single result set for each row in TableList - you probably don't want to do this for more than about 50 or 100 tables since the output might become unmanageable. If you have a lot of tables, you may want to consider inserting the output into a temporary table, then running a single select statement against that table.

If the structure of the tables listed in the TableName column are identical, you can use a UNION ALL to join the output into a single resultset:

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) 
    + 'UNION ALL' + CHAR(13) + CHAR(10) END 
    + 'SELECT ''' + tl.DatabaseName 
        + '.' + tl.SchemaName 
        + '.' + tl.TableName 
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName) 
        + '.' + QUOTENAME(tl.SchemaName) 
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

Be aware that UNION ALL is generally much faster than UNION since the ALL option doesn't need to run a DISTINCT operator over the data.

share|improve this answer
4  
Yes, this is the approach I often use when I need to build dynamic commands that repeat an operation against multiple tables, procedures, foreign keys, etc. But for the OP and for @VladimirS., this is still a cursor, just without the DECLARE CURSOR or WHILE code. It doesn't really matter whether you use an explicit cursor or not to build the code you eventually run - the string concatenation during the cursor is going to be a drop in the bucket compared to whatever you're ultimately going to do against all of those tables. – Aaron Bertrand Mar 21 at 19:09
1  
good point - running the generated code will take a lot longer than generating the statements! – Max Vernon Mar 21 at 19:12
    
Does it help/hurt to put a UNION ALL between the queries? – Kenneth Fisher Mar 21 at 19:43
    
I'm curious if the UNION ALL would have any effect on performance. I'm inclined to think it won't but I'm not certain. cc @AaronBertrand – Kenneth Fisher Mar 21 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.