7 Replies - 616 Views - Last Post: 14 November 2019 - 09:26 AM

#1 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Querying multiple identically structured databases w/o hardcoded names

Posted 06 November 2019 - 02:23 PM

I hope that title makes sense.

This is a conundrum I've been grappling with for years. I'm hoping someone can help me make this significantly more efficient.

We have a set of databases thata are updated on-demand (usually once daily, at 3am) by one of our production systems. Each database is named for a client. For example,

Dbase_client1
Dbase_client2
Dbase_client3

and so forth.

The system I've inherited, refined, and maintain/improve uses a local (to its sql instance) copy of that same DB, with an additional client field/key on every table to determine which client the data belongs to. I have no control over the processes involving those DBs or the software that updates them. Previously I've been consolidating them to a single DB so that I can report on everything in one go.

Currently, I've got a FileSystemWatcher running on a process that triggers an agent job when a given db finishes updating. That job runs a handful of procedures that long along the lines of https://pastebin.com/5Vub5VH5

That's obviously made generic, but hopefully conveys what I'm doing. That's been running fine since I implemented that method several months ago, but recently it's started causing some sort of collision with the first part of the DB update process (our prod system dump to the individual client sql dbs) that runs nightly.

What I would like to do is be able to query those individual client DBs without having to load the data over at all.

Something along the lines of
declare @resultstable table (resultsfields types)

declare clientdbcursor cursor for select clientcode from dbcodes
open clientdbcursor
fetch next from clientdbcursor into @client

while @@FETCH_STATUS = 0
BEGIN
   
insert into @resultstable
select 
Field1, Field2, etc
from Server.Dbase_ + @client + .dbo.table
where criteria = 'met'

fetch next from clientdbcursor into @client
END
close clientdbcursor
deallocate clientdbcursor

select * from @resultstable



Naturally I realize that won't work that way. But if I could iterate through the DBs collecting only the data I need, it would mitigate the need to pull the data locally and solve a lot of headache for me. So please, SQL has never been my strong suit. How can I make this better?

I had to put the first part into a pastebin because it was triggering security like I was trying an injection attack

heh...oops?

Is This A Good Question/Topic? 0
  • +

Replies To: Querying multiple identically structured databases w/o hardcoded names

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2996
  • View blog
  • Posts: 11,539
  • Joined: 03-December 12

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 06 November 2019 - 02:32 PM

If Iím reading correctly, itís just dynamic sql. When Iíve done that previously, we used SSIS for it. A stored procedure that takes in the table name should suffice, it is dangerous, but itís been done before.
Was This Post Helpful? 1
  • +
  • -

#3 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 06 November 2019 - 02:55 PM

I'm not really familiar with SSIS beyond the very basics, where would I get started figuring out how to do that? Part of the reason I'm asking here is I just don't know where to even begin in terms of what functionality I might use.

Like right now I have that part in the pastebin as a stored procedure that takes in the database name. So I open a cursor on the dbcodes, and then pass it in, and @client is the dbcode - then it executes the code dynamically.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2996
  • View blog
  • Posts: 11,539
  • Joined: 03-December 12

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 06 November 2019 - 04:01 PM

It's essentially the same as you are doing, I'll have to do some digging to see what I can find. But, in the SSIS config you have a list of the databases you want to use as variables, and it would call the sp with each of those. The other side of that, is to make a step for each database; it gives leverage over inclusion or exclusion based on that alone. Then a finishing step that would handle your aggregation and clean up processes.
Was This Post Helpful? 1
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7500
  • View blog
  • Posts: 15,541
  • Joined: 16-October 07

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 07 November 2019 - 03:14 AM

First, I'd advise against declare @resultstable table . MSSQL temp tables seem significantly more robust than vars. Also, just because you're not explicitly creating a table, doesn't mean you're not impacting temp storage, rollback, resources in general. At least with a table, you can clean that rollback occasionally.

Honestly, this looks like a job for something outside the database. I mean, if you're implementing all that dynamic SQL to do the job on the SQL Server, you've lost a lot of the advantage of using the server in the first place.

It looks like your end goal is some kind of report? I'd probably write a powershell script (or function) that generates the report for a single client. Then another method to pull a list of clients, mush all the results for each client together, and spit out the desired report.
Was This Post Helpful? 1
  • +
  • -

#6 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 07 November 2019 - 06:06 AM

View Postbaavgai, on 07 November 2019 - 05:14 AM, said:

First, I'd advise against declare @resultstable table . MSSQL temp tables seem significantly more robust than vars. Also, just because you're not explicitly creating a table, doesn't mean you're not impacting temp storage, rollback, resources in general. At least with a table, you can clean that rollback occasionally.

Huh. So I looked up "mssql temp table vs var table" cause I thought a var table was a temp table. I had no idea - and that's what I mean when I say I don't even know what to ask. Thank you! I'm going to do some more reading on that to get a better grasp of them before I start messing with them, but if it reduces the load on the server that's part of my goal.

Quote

Honestly, this looks like a job for something outside the database. I mean, if you're implementing all that dynamic SQL to do the job on the SQL Server, you've lost a lot of the advantage of using the server in the first place.
That's part of why I'm consolidating them. The databases are reloaded from a vendor every night, so I can't do things like build additional indicies or even stored procedures to get what I want. What I was thinking to do was use the local DB that is currently working as a consolidated copy of the others, as...I guess kind of a viewer? Since I'm not needing to manipulate the data, just validate against it.

Quote

It looks like your end goal is some kind of report? I'd probably write a powershell script (or function) that generates the report for a single client. Then another method to pull a list of clients, mush all the results for each client together, and spit out the desired report.

Yes and no. Building reporting is my current project but I use the data in a number of ways, like automatically resending something that doesn't appear to have gone through, or populating lists of options in a program from enabled entries in a table, and such. And that could work, but I'm really trying to avoid having 30 different copies of the exact same stored procedure to collect data with just the dbname changed.
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2996
  • View blog
  • Posts: 11,539
  • Joined: 03-December 12

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 08 November 2019 - 04:03 PM

Depends on purpose. If multiple processes will be using the same table and aggregating against it, a temp table is probably what you want, because it does write to disk. If however you just need the table for that procedure AND the size of that table won't be a large recordset, a variable table is more inline with desired functionality.


Quote

That's part of why I'm consolidating them. The databases are reloaded from a vendor every night, so I can't do things like build additional indicies or even stored procedures to get what I want. What I was thinking to do was use the local DB that is currently working as a consolidated copy of the others, as...I guess kind of a viewer? Since I'm not needing to manipulate the data, just validate against it.


ETL processes then. Are the tables themselves redone? Or is the data they hold updated? I think it would help to know the full complexity of what you are after and what allowance you do and do not have. I would advise against doing something like drop table every night, though truncating may make sense.

Manufacturing has a lot of these kinds of gotchyas due to keeping records in sync for ERP and other systems.
Was This Post Helpful? 1
  • +
  • -

#8 depricated   User is offline

  • Nero


Reputation: 2488
  • View blog
  • Posts: 6,185
  • Joined: 13-September 08

Re: Querying multiple identically structured databases w/o hardcoded names

Posted 14 November 2019 - 09:26 AM

The tables themselves are redone every night.

I set it up as a dynamic query as suggested, and converted my var tables to temp tables. I've got most of the relevant procedures rewritten with only 3 big ones left to tackle now.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1