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 |
|
bjornh
Yak Posting Veteran
87 Posts |
 Posted - 2003-03-15 : 08:08:50
|
| hi,I've created a database with partitioning in the same way as it is described in this article. http://www.sqlteam.com/item.asp?ItemID=684But when I create a query that uses a variable instead of a fixed number, All the tables in my partitioned view are appearing in my Execution plan.so, SELECT * FROM Subdivision WHERE SubdivID = 9538 display's only 1 table in the E.P. but SELECT * FROM Subdivision WHERE SubdivID = @var display's all the tables in the E.P. There isn't any data collect from the other tables, but as I add more tables, my execution times increases, and I don't it...Thanks,Bjorn |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-15 : 08:35:50
|
| The optimizer can optimize a literal value at compile time, but it can't do that with a variable, therefore it has to generate a plan that encompasses any value that might be passed through the variable. This is perfectly normal behavior.If you're delivering a letter to a house and the letter has the house number, it's easy. But if it only has the person's name, you have to go to every house and ask. Same situation with your query. |
 |
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-03-15 : 08:38:29
|
| But is it normal then that with every table I add that the parse time on my website is increasing with 0.1 sec?Bjorn |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-15 : 08:48:33
|
| Yes. If they add houses to the street where you're delivering the letter, you have to check every one to ensure you found the right person.BTW, parse time is insignificant. 0.1 seconds is not going to be noticeable to anyone. Worry more about the actual execution time, not just what the optimizer reports back to you.Just out of curiosity, how many tables are in this view, and how many rows are there in total? Why are you still adding tables to this view? What is/was the reasoning for separating the data into different tables? And if there are less than 1 million rows in total, can you try testing a single table solution? The point is, if you continue to add tables to this the query optimizer won't have any choice but to keep adding them to the execution plan, and execution time will probably continue to increase. This is especially true if the tables are all stored on the same file or filegroup, because SQL Server can't read them in parallel.Edited by - robvolk on 03/15/2003 08:48:53 |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-03-15 : 09:33:29
|
to begin, the parse time was the time calculated by ASP, so it is the actual time (sorry, my mistake)And now, I shall tell my story about this db design...  The db is used for a website statistics application. People can register on our site, and then they can put a script on there website etc. (you know how it works i think) Every user has 3 tables. 1 for the pageviews, named log2 (columns: id, siteID, date, vistorsID, pageID, all int type except date of course) siteID contains a constraint for the partitioned view, visitorsID is used to know what user hit what page. And pageID reffers to table Pages (columns: id, name) the 3e table (log1) is for all the data that is collect from the users (columns: id, siteID, entry datetime, exit datetime, ip, browserID, osID, colorID, resolutionID, refferID, searchengineID, keywordID, countryID, EntryPageID, exitpageID)all the columns with ID are int (or smallint/tinyint) and reffer to some tables.And, I created a SP that add's the 3 tables on a new client registration and recreate the view (it loops to the client table, and creates a varchar that is then executed when all the clients are in it. log1 view looks like this:SELECT *FROM log1_258679UNION ALLSELECT *FROM log1_258686....If tryed many db-design's, and this one came up as best (at least, i thought  ) I do have another design, that uses dynamic query's on all SP's. But i can't created all the features i want into this design (getting the users clickpath, and sort out what clickpath is populair.)so, now you know my story, what's your advice o great big Yak...  BjornEdited by - bjornh on 03/15/2003 09:34:57 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-15 : 10:17:55
|
| If you're storing the siteID, which I assume identifies each registered user, why are you creating separate tables for each of them? Just set up 3 tables and log everything in there normally. If siteID is a different attribute, and you don't have a column that stores the user's ID, add one to each table and go from there. You'd eliminate the need for a view and you'll drastically cut down on the table maintenance nightmare (if you're not experiencing it now, you will soon) You can index the single log1 table any way you like and will see much better performance than a partitioned view would provide.Generally speaking, having multiple tables storing the same information defeats the purpose of having a relational database. The ONLY time you'd use that is if security is absolute essential, and even that is a debateable reason. Partitioning data does not always lead to a performance improvement, and it really won't work for your database structure. |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-03-15 : 10:30:38
|
| ok, thanks, just one question left for me. What if i would have let's say, 2000 customers (I like dreaming) and all these sites are pretty high traffic. So, i will end up with 10 milion rows in log2 for example. are indexes so powerfull that it doesn't matter if there are 9,9 milion unused rows (for that particular query than) and still give high reaction speeds?Bjorn |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-15 : 10:39:57
|
quote:
If you're storing the siteID, which I assume identifies each registered user, why are you creating separate tables for each of them? Just set up 3 tables and log everything in there normally. If siteID is a different attribute, and you don't have a column that stores the user's ID, add one to each table and go from there. You'd eliminate the need for a view and you'll drastically cut down on the table maintenance nightmare (if you're not experiencing it now, you will soon) You can index the single log1 table any way you like and will see much better performance than a partitioned view would provide.
I thought this all sounded familiar!!http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23310&ARCHIVE=&whichpage=1- Jeff |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-03-15 : 10:53:52
|
Aah yes, whaha i thought i only talked about partition views, but all in one table had come across.... pfff sorry for dubbel posting... i will do some more tests. Because now it is partitions or 1 table. Or find another way to get the clickpath...thanks jeff for reminding to myself (or something like that...  ) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-15 : 11:17:04
|
| Bjorn -- hopefully I didn't come accross as "why are you double-posting!?" ... I just read the posts from you and Rob and as soon as I heard Rob's suggestion about putting all the data in one table I thought "that sounds familiar" ...This is a great follow-up thread to the earlier discussion, I think !!- Jeff |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-03-15 : 11:21:50
|
| Well, if I had read my early topics, I didn't had to post the 10 milion question.... But at least I now know your not mad at me... :)byeBjorn |
|
|
|
|
|
|
|
|
partitioning; but why is there preformance loss