Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
panmaj
Starting Member
4 Posts |
 Posted - 2011-01-13 : 12:53:20
|
| Hi all,I've been given the task of architecting a SQL Server DB that's been around for a few years. There are three main tables in it: Table A with a list of products and product attributes (~9000 rows), Table B with a list of applicable countries where these products are sold (~40 rows), and Table C that holds the number of products sold in each country by year (30 years of data for a total of ~360,000 rows). Right now, Table C is set up as follows:TableA_ProductID | TableB_CountryID | TableC_Sales1995 | TableC_Sales1996 | TableC_Sales1997...I understand that in theory, this is bad practice as every year the tables and related views will have to be altered to include new years each year, so I tried to redesign Table C as follows:TableA_ProductID | TableB_CountryID | TableC_Year | TableC_Sales |The drawback of this design is that there are way too many rows in this table (~10,000,000) for this table and other views to be calculated quickly, even when indexed.Q: Is there another way to design this database that wouldn't result in so many rows in the third table, or otherwise would speed up the data retrieval time?Thanks,Peter |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
panmaj
Starting Member
4 Posts |
Posted - 2011-01-13 : 14:46:45
|
| Hmm - OK. Thanks for the response, Tara. I tried using the Database Engine Tuning Advisor to optimize the indexes using a common set of queries I'd run, and after implementing the indexes and statistics that it recommended, I still didn't see a real gain in performance time. It takes 36 seconds to run the query on the first table/associated views with no optimization at all, and it takes roughly two minutes to run a straight select on the table, and more time if more complex associated views are introduced.The server is an 2007 Enterprise machine with 4 GB of RAM with a quad core Xeon processor @ 3.16 GHz virtualized with VMWare. Maybe I'm missing something? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
panmaj
Starting Member
4 Posts |
Posted - 2011-01-18 : 13:38:41
|
Thanks again. Here is a sample query, with view blah being a join between the three tables on countryID and modelID.select ModelID, Status, DateModified, VendorID, countryID, Year, apfrom blahwhere year between 1994 and 2009 This returns 5,500,000+ rows in 1 minute, 13 seconds - not sure if this is supposed to be fast or not?There are the indexes on the primary keys of the products/countries/sales tables, and there is an additional index on the sales table that is indexed on the countryid, year, modelID, and salesID columns (the last one being an arbitrary identity column representing the natural composite key of countryid + year + modelID). This index includes the sales numbers (ap) in the included column list.Below is the execution plan and statistics. I didn't see a way in the forum to post images, so I made the text exec plan below.Execution PlanIndex Seek (nonclusteredindex on tblblah(bigtable)) (cost 35%) || index scan on small countries table (Cost 0%)||vnested loops (inner join)(cost 28%) || Clustered index scan on products table (Cost 1%) ||vHash match (inner Join)(Cost 37%)||vSelect (cost 0%) StatisticsClient Execution Time 10:15:19 Query Profile Statistics Number of INSERT, DELETE and UPDATE statements 0 0.0000 Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000 Number of SELECT statements 2 2.0000 Rows returned by SELECT statements 5503057 5503057.0000 Number of transactions 0 0.0000Network Statistics Number of server roundtrips 3 3.0000 TDS packets sent from client 3 3.0000 TDS packets received from server 50406 50406.0000 Bytes sent from client 404 404.0000 Bytes received from server 2.064547E+08 206454700.0000Time Statistics Client processing time 21436 21436.0000 Total execution time 21591 21591.0000 Wait time on server replies 155 155.0000 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
panmaj
Starting Member
4 Posts |
Posted - 2011-01-18 : 14:41:40
|
| Right, that's what I was thinking, but I was hoping there was some miracle ability of SQL Server that I didn't know about. The data is being fed into an Excel pivot table, where the users would like to be able to pull in any arbitrary segment of this data on demand. I'll keep chewing on this.Thanks, |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Database Setup