Query 5000 Tables for data

Question

Post reply

Query 5000 Tables for data

sgj31

Old Hand

Points: 334
More actions
April 16, 2010 at 2:54 pm

#140390

I have a client that has 5000 customers and in the database each customer has a table.
dbo.Customer1
dbo.Customer2
...
dbo.Customer5000
I need an efficient way of reporting on these tables without looping through each table to get the information on each customer. I attempted creating a view, but a view with 5000 UNION ALL's is not efficient. I'm looking for suggestions.
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

How much data?

How often does it change?

Is a snapshot ok?

Are all the tables identical schema wise?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dugi SSCoach Points: 17998 More actions · Answer 2

Interesting situation, I can see that for every new customer, the new table will created ...will it continue or it stops at 5000th customer's table, and you just trying to read data from these tables!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

sgj31 (4/16/2010)
I have a client that has 5000 customers and in the database each customer has a table.
dbo.Customer1
dbo.Customer2
...
dbo.Customer5000
I need an efficient way of reporting on these tables without looping through each table to get the information on each customer. I attempted creating a view, but a view with 5000 UNION ALL's is not efficient. I'm looking for suggestions.
Thanks in advance.

I don't believe that a view with 5000 UNION ALL's will actually work. IIRC correctly, SQL Server 2000 had a limit of something like 243 UNION ALL's and I don't know if that's changed or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgj31 Old Hand Points: 334 More actions · Answer 4

Garadin (4/16/2010)
How much data?
How often does it change?
Is a snapshot ok?
Are all the tables identical schema wise?

Approx 9000 rows in each table. All tables are the same schema wise. The data can change daily and they keep adding a new table for every new Customer. They get 1-3 new customers a month.

They are expecting on-demand reports, which I can provide, but the backend sql takes anywhere from 10 - 30 minutes to loop through all the tables and this is not acceptable to the users.

I have never seen a database designed like this so I'm at a loss for handling the data. Most of the processes use dynamic sql to decide which customer table to update. I'm using the same logic for reports. I will suggest a snapshot of the data that refreshes once or twice a day and see if that will be close enough to real-time for the users. I imagine it won't....darn this world of instant gratification!

LutzM SSC Guru Points: 107049 More actions · Answer 5

All I can think of is to use the undocumented sp_foreachtable (google for details) which really is looping through the tables.

I'm not sure though, if this database design really is the best posiible solution...

Especially, if (some) data need to be queried that are related to more than one customer.

I don't know enough regarding the business case but I probably would have used a master table holding all the customer data and add views per customer if needed.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

Build another table. Copy the data from all the individual tables into the central table and add a customer id, if there isn't one already. Report from the combined table.

You could do this periodically, or put triggers on the individual tables that replicated the DML into a permanent combined table.

And change whatever goofy app they're using to work off the combined table.

And please let us know the name of the guy who designed this so no one makes the mistake of hiring him.

And then again, I might be wrong ...
David Webb

sgj31 Old Hand Points: 334 More actions · Answer 7

David Webb-200187 (4/16/2010)
Build another table. Copy the data from all the individual tables into the central table and add a customer id, if there isn't one already. Report from the combined table.
You could do this periodically, or put triggers on the individual tables that replicated the DML into a permanent combined table.
And change whatever goofy app they're using to work off the combined table.
And please let us know the name of the guy who designed this so no one makes the mistake of hiring him.

Excellent suggestion. Thanks.

Garadin One Orange Chip Points: 29613 More actions · Answer 8

You *may*, emphasis on *may* get better performance creating a bulk staging table where you copy all the data into... then create indexes on it, and run your queries off of that. If they're ok with the data being a snapshot of the night before, you could populate this table overnight at a low usage time and then run a much faster query off of the single table.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dugi SSCoach Points: 17998 More actions · Answer 9

9000 * 5000 = 45 000 000 records and growing up if they have new customers hahaa you should look for the nice performance with those records in a one table, sure if you have HDD space...

and sure that I vote for this:

...And please let us know the name of the guy who designed this so no one makes the mistake of hiring him.

:w00t::w00t::w00t::w00t::w00t::w00t:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 10

I think the only way to do this is David's suggestion, which is essentially a data warehouse. Move the data over periodically, every hour or so, and build reports off something that makes sense.