July 7, 2012 at 9:20 am
I have a query that retrieve data from multiple large base tables. Those tables already have indexes. I am wondering is there anything in SQL server like Materilized views in oracle where we can stored the data into another physical location that update dynamically and then retrieve rows from there.
Please let me know if any suggestion to optimize query?
July 7, 2012 at 9:32 am
Yes there are materialized views in SQL Server. They are most commonly called Indexed Views.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 7, 2012 at 9:47 am
Thanks for responding..Any suggestions how to create index view on below query.
SELECT
CASE
WHEN a.ValueKey LIKE '%Description%' THEN 'Additional Description Changed'
WHEN a.ValueKey LIKE '%Box Number%' THEN 'Box Number Changed'
WHEN a.ValueKey IS NULL AND a.AuditId = 10 THEN 'Description Changed'
WHEN a.ValueKey IS NULL AND a.AuditId = 248 THEN 'Physical Object - Attribute Changed'
ELSE
a.ValueKey
END AS AttributeChanged,
a.DataId AS DataId,
a.AuditDate AS AuditDate,
e.BoxNumber AS BoxNumber,
b.Name AS BoxName,
d.FirstName + '' + d.LastName as "Performer",
Cast(a.Value1 As varchar(2000)) "Before",
Cast(a.Value2 As varchar(2000)) "After"
FROM
table1 a
INNER JOIN table2 b ON a.DataID = b.DataID
INNER JOIN table3 c ON a.DataID = c.NodeID
INNER JOIN table4 as d on a.performerid = d.id
INNER JOIN view1 e ON b.DataID = e.NodeID
WHERE
a.SubType = 424 AND
(a.AuditID = 10 OR a.AuditID = 248) AND
(a.Valuekey LIKE '%string1%' OR
a.ValueKey LIKE '%string2%' OR
(a.valuekey IS NULL AND
a.value1 IS NOT NULL))
In above query view1 is not created with SCHEMABINDING option... I am able to create a view for above query but without schemabinding ..with this option its not letting me know to create view...
after that when i am trying to create index i am getting below error..
Error: Cannot create index on view 'vw_box_audit' because the view is not schema bound...
one more question: Is index view dynamic..means if we create it then we don't need to refresh data..would it automatically refresh data?
July 7, 2012 at 9:57 am
Why do you want to create an indexed view? Have you exhausted every other option in tuning and have you considered all the restrictions and requirements for indexed views?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2012 at 11:52 am
Hi Gail.. Please let me know if you have any suggestions... Actually, i created materialized views in oracle at this situation. So that's why i may want to choose this. Please let me know if you have any suggestions.
July 7, 2012 at 11:56 am
You have view in the query so it is not possible to create the indexed view.Also, indexes views are dynamic and are refreshed automatically.But it has many more restriction.
For more restriction have a look at the link
http://msdn.microsoft.com/en-us/library/ms191432.aspx
Also, it seems like that the filtering happens on table 1 mainly as all SARG are on that table. How many rows are after the filtering the data on table 1.Also how much time it takes and how much resource it is using on your server(liek IO,cpu time ,memory etc).
Maybe it is possible to tune the existing query so that you do not need a indexed view.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 7, 2012 at 12:05 pm
ashok23_sharma (7/7/2012)
Hi Gail.. Please let me know if you have any suggestions...
Indexes on the base tables? Possibly full text indexing for the string wildcard filters, would depend on the table and what the exact filters are (I doubt they really are '%string1%'
Actually, i created materialized views in oracle at this situation. So that's why i may want to choose this. Please let me know if you have any suggestions.
SQL Server <> Oracle. What works best in Oracle is not necessarily going to work best in SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2012 at 9:17 pm
Full text indexing is the way to go for your requirement, unless you can change your requirement,I.e. remove the starting %, which I always oppose.
(a.Valuekey LIKE '%string1%' OR
a.ValueKey LIKE '%string2%' OR
July 7, 2012 at 9:28 pm
Hey Lokesh..I can't ignore these condition 🙁
(a.Valuekey LIKE '%Description%' OR
a.ValueKey LIKE '%Box Number%' OR
(a.valuekey IS NULL AND
a.value1 IS NOT NULL)
July 7, 2012 at 9:35 pm
Go for full text indexing, remember to consider the overheads, please spend some time and read then consider all the facts before implementing.
July 7, 2012 at 9:53 pm
yes, thats why i am looking for another options available ..like index view..let me know if you have any suggestions
July 7, 2012 at 11:30 pm
Post the plan of your query and ddl for tables.People might be able to help u on getting your query optimized..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 8, 2012 at 12:02 pm
I edited query..even below query not returning count after more than 1 hour...not able to count total rows as well on below table..but seems it's millions of rows.
SELECT
COUNT(1)
FROM
DAuditNew a
WHERE
a.SubType = 424 AND
(a.AuditID = 10 OR a.AuditID = 248) AND
(a.Valuekey LIKE '%Description%' OR
a.ValueKey LIKE '%Box Number%' OR
(a.valuekey IS NULL AND
a.value1 IS NOT NULL))
July 8, 2012 at 12:10 pm
Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2012 at 12:15 pm
Gullimeel (7/7/2012)
Post the plan of your query and ddl for tables.People might be able to help u on getting your query optimized..
are you able to provide a " create table" script for DAuditNew?
how many rows in this table?
can you tell us what indexes are on table DAuditNew...script if able please.
can you provide any execution plan(s) for your query?
if you are not sure about request, please post back, and someone will help
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply