May 31, 2011 at 2:52 pm
Hi there, I'm trying to see if it's possible to optimize a query that is taking 5 seconds to run:
SELECT * FROM [vwUnionAllFields] WHERE [MLNumber] = 123456
vwUnionAllFields is a view that does UNION ALL on 18 tables.
Each of these tables contains a clustered index (MLNumber), so when I do the select, the MLNumber field I'm using is just that clustered index for each of the 18 tables.
The execution plan shows a Clustered index seek operation on each of the 18 tables.
And that's as far as my troubleshoting knowledge goes, so I'm not sure if it's possible or what could I do to make it faster, provided I'm querying for a clustered index.
The tables are wide, each has hundred of columns. The total amount of rows across the 18 tables is 10.000 records.
Thanks for sharing your thoughts 😉
Agustin.
May 31, 2011 at 4:06 pm
agustingarzon (5/31/2011)
Hi there, I'm trying to see if it's possible to optimize a query that is taking 5 seconds to run:SELECT * FROM [vwUnionAllFields] WHERE [MLNumber] = 123456
vwUnionAllFields is a view that does UNION ALL on 18 tables.
Each of these tables contains a clustered index (MLNumber), so when I do the select, the MLNumber field I'm using is just that clustered index for each of the 18 tables.
The execution plan shows a Clustered index seek operation on each of the 18 tables.
And that's as far as my troubleshoting knowledge goes, so I'm not sure if it's possible or what could I do to make it faster, provided I'm querying for a clustered index.
The tables are wide, each has hundred of columns. The total amount of rows across the 18 tables is 10.000 records.
Thanks for sharing your thoughts 😉
Try this... write the query against the base tables retrieving only the columns you need (as opposed to select *) join by MLNumber. What happens now?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 31, 2011 at 4:09 pm
How the heck can those 18 tables have exactly the same columns and same meaning???
May 31, 2011 at 4:30 pm
Each of the tables have more or less columns, with different names.
The UNION ALL puts them all together in one place, the data types and the meaning is the same.
I can't run the query against the base tables, provided the column names differ from table to table. Can you ellaborate a little more on your suggestion ? Does it have to do with the amount of columns (over a hundred columns) and how ?
I'm interested in retrieving ONE row only, the queries on that view are to retrieve one record at a time with all the columns, querying by the index, and make that as fast as possible, durrrr :s
May 31, 2011 at 4:40 pm
Gods, this screams ORM tool (property.boat, property.lotl, property.mult, property.park...). It's inheritance and polymorphism in table format. ka-click, bang.
This is an estimated query plan, can we see the actual? Also, can we see the vw_unionallfields definition? Is MLNumber actually supposed to be MLSNumber?
Some of this you can't fix unless the un-3part named tables are also in database WILC.
A clustered index seek for a non existant value will always need to go to the leaf level to confirm there's nothing there. Try using an NC index on all the tables for just MLNumber to allow it to drive through the NC to confirm there's no row to bother doing a key lookup on. You'll find some speed out of that.
After that? Gyeah... Um... yeesh.
Yeah. I got nothing. This is ugly. Maybe I'll have some more ideas after I see the view definition and can try to spot something in the actual plan... in particular why some of these are doing double compute scalars and the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 31, 2011 at 5:04 pm
I'm attaching the actual exec plan and the view def, hehehe go nuts !
What do you mean with un-3part named tables ? All the tables are in the same database.
So adding an NC for MLNumber besides the clustered index ? I will try that tomorrow. But it's slow pulling existing values.
May 31, 2011 at 5:07 pm
It's just a naming convention, there are tables named like
WILC.Property.LAND
and tables named like
Property.LAND
May 31, 2011 at 5:16 pm
agustingarzon (5/31/2011)
I'm attaching the actual exec plan and the view def, hehehe go nuts !What do you mean with un-3part named tables ? All the tables are in the same database.
So adding an NC for MLNumber besides the clustered index ? I will try that tomorrow. But it's slow pulling existing values.
CompileTime = 3386 !!
over 7500 scalars !!
Rethink the logic - no index can save that view!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 31, 2011 at 5:38 pm
mister.magoo (5/31/2011)
agustingarzon (5/31/2011)
I'm attaching the actual exec plan and the view def, hehehe go nuts !What do you mean with un-3part named tables ? All the tables are in the same database.
So adding an NC for MLNumber besides the clustered index ? I will try that tomorrow. But it's slow pulling existing values.
CompileTime = 3386 !!
over 7500 scalars !!
Rethink the logic - no index can save that view!
What MM is talking about, and is a great catch, is this line in the XML of the sqlplan:
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="7448" CompileTime="3386" CompileCPU="3303" CompileMemory="43240">
Note the Compile time. That's 3.3 seconds to compile. It *should* re-use the plan though. What kind of time do your second and further iterations take?
If you compile this with a parameter in a stored procedure, that compilation time should be re-used. However, it's going to be a bit flakey with that kind of union involved.
The 3 part naming I emant was this:
FROM [WILC.Property.LANL]
compared to:
[Property.RESI]
Due to the inconsistency I wanted to make sure this was all in one db.
I didn't realize that was the full table name, not a DB.Schema.Table.
This is a schema shoe-horn query, for lack of a better name. One size will fit all, come heck or high water. You're going to pay for these no matter what you do. I would personally completely redesign the schema here.
I'd start with a base 'property' table, holding MLNumber and any other common fields (figure 90% hit rates), and some kind of 'category' field. Then I'd break out to supplementary tables that would return the rest of the necessary data depending on what was being searched, tightening up the schema.
Now, doing that means completely overhauling the entire system's logic. That may not be available to you. In that case, NC indexing for seek/lookups with dead rows off the specific NC's not needing to go through all the levels you will with a Clustered, and using a proc to allow for parameterized compilation for consistent re-runs, or at least parameterize the call. I think the latter will gain you more then the former, but both should help.
The query cost itself isn't too bad, a mere 0.059. That compilation is killing you if this is called regularly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 9:03 am
agustingarzon (5/31/2011)
I can't run the query against the base tables, provided the column names differ from table to table.
Yes you can, just write it. 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 1, 2011 at 11:00 am
Guys I really appreciate your suggestions and comments. You get me going in the right directions really quick.
I'll try Craig's suggestion first, to see if I can gain some milliseconds using a stored procedure and a NC index.
After that and if that doesn't work, I'll have to take the hard way and refactor / reengineer the whole monster.
The first I'll do is figure out a way to get a list of the more popular fields, that should help trimming most of the fat from that view and the underlying tables.
It's being a delight as usual :: )
Cordially,
Agustin.
June 25, 2011 at 1:44 am
Agustin,
Ideally you should be able to avoid 18 table clustered index seek to 1 table only as all 18 tables have same no of columns. This is assuming you have id range divided in 18 tables.
See this example of how manually its done in sql 2000 when table partition was not introduced...
http://msdn.microsoft.com/en-us/library/ms190019.aspx
It still works in 2005/2008 (old method yet works on standard edition)
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 25, 2011 at 12:46 pm
I believe that setting up the view as a "Partioned View" may help... a LOT!. Please lookup "Partitioned View" in Books Online on how to correctly set one up and what the requirements for the underlying tables are.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2011 at 5:25 pm
bleh...
it seems all the cool features require the Enterprise Edition of Sql Server :^s
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply