September 14, 2008 at 11:52 am
OK, so what you are saying is that I should merge all of the 100 odd tables into one table? That would not be too difficult as the tables all have the same field names. The only problem is that they all have seperate IDs (i.e. California has IDs from 1-n, and Florida has IDs from 1-n aswell), therefore I would need to create a new primary key ID field to insure unique IDs for every record.
Are you sure that SQL Server will search out all of the records of a particular state from one single table just as fast as simply retreiving all records from the state table? I suppose I would need to have a "State_ID" field with an index, but even with this will it really be just as quick?
What is the SQL command to merge all 100 tables into a single table? Assuming they all have the same fields.
September 14, 2008 at 11:58 am
I wouldn't want to be stuck either at picking a town from a list of 10 000 names in a combo or listbox. I think you'd be better of using a search GUI to ease off the process. (pick state from combo, then type search query for the town, then pick from the results of that search).
Wow, thank you for being so detailed.
But remember that "Cities" is just an example, not what I am actually doing. Though I have thought of this, and there is another drop-down box to narrow down the search, call it "Counties" in our example, did not mention it before because there is no reason to over-complicate the question with more detail that is not anything to do with the particular problem at hand.
September 14, 2008 at 1:06 pm
There's only 1 answer to that problem. Test both solutions, see which one performs the best, them implement that one.
Like I said 500 000 records is not a lot for SQL SERVER if all the right indexes are in place. However 10 000 rows to sift through for the user is a lot... even 500 is a lot. I would hate to have to do that all day long when I could simply type in a few characters and have the server narrow the results down to nothing... or as close to that as possible.
One of the rare time I saw partinioning like this done was for a majog phone campany. They had like 10 or 50 millions calls / day. So they had 1 table per day... but your numbers are far from that.
Do you expect the amount of data to go as high as this (or at least in the millions)?
September 14, 2008 at 1:15 pm
One last thing to consider... what will you do when someone asks you to write a report that picks a subset of the data in any of the states (determined only when the report is run)? You query would look something like this...
SELECT WHATEVER FROM dbo.tbl001 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl002 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl003 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl004 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl005 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl006 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl007 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl008 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl009 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl010 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl011 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl012 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl013 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl014 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl015 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl016 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl017 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl018 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl019 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl020 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl021 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl022 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl023 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl024 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl025 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl026 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl027 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl028 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl029 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl030 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl031 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl032 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl033 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl034 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl035 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl036 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl037 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl038 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl039 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl040 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl041 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl042 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl043 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl044 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl045 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl046 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl047 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl048 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl049 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl050 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl051 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl052 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl053 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl054 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl055 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl056 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl057 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl058 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl059 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl060 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl061 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl062 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl063 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl064 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl065 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl066 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl067 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl068 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl069 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl070 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl071 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl072 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl073 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl074 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl075 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl076 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl077 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl078 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl079 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl080 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl081 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl082 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl083 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl084 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl085 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl086 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl087 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl088 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl089 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl090 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl091 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl092 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl093 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl094 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl095 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl096 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl097 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl098 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl099 WHERE SearchOptions = @AreHere UNION ALL
SELECT WHATEVER FROM dbo.tbl100 WHERE SearchOptions = @AreHere
September 14, 2008 at 3:24 pm
OK Thanks for that, but how would I merge all the tables into a single table using SQL Server?
September 14, 2008 at 6:00 pm
Create table states...
Insert into dbo.states (Columns list) values ('whatever needs to be here')
Create master table
Insert into master table (Select Columns List, ForeignKey From States from table1)
...
Insert into master table (Select Columns List, ForeignKey From States from table100)
September 15, 2008 at 9:50 am
Ummmm... wouldn't a nice partitioned view work well here?
--Jeff Moden
September 15, 2008 at 10:10 am
I never worked with that... I'll let you explain that one :D.
September 15, 2008 at 5:42 pm
Basically, it's just like what's there already... a bunch of identical tables with different ranges of a column or columns. You create a view using SELECT/UNION just like you did in your example code and you end up with a very smart view that will only lookup in the tables that are needed to meet criteria when selecting against or updating the view.
--Jeff Moden
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply