August 11, 2011 at 1:33 pm
Is any thing need change with the code to improve the performance of query to retrieve data from view very faster. I don't know how to attach the code separately. That the reason i am pasting here.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object: View [dbo].[vw_bla_trans_all] Script Date: 03/05/2009 09:14:23 ******/
CREATE view [dbo].[vw_bla_trans_all]
as
select fbt.*
from LOANACTDM.dbo.fact_bla_trans fbt (NOLOCK)
inner join udp_cust_params ucp
on fbt.cust_no = ucp.cust_no
where isnull(channel_cd,' ') =
case when fbt.cust_no != '0306A' THEN isnull(channel_cd,' ')
when isnull(channel_cd,' ') in (
'Correspond',
'Correspondent',
'Retail',
'Wholesale'
) then isnull(channel_cd,' ') ELSE 'IGNORE CHANNEL'
END
GO
I am not able to use any indexes on view, i need to use schema binding concept on view when created.
But i am not able to do that. Because tables are from different databases.
Will any one help me to improve the logic on query, that would be great.
August 11, 2011 at 1:39 pm
Please don't use no locks without knowing all the dangerous consequences : http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
If you can't change anything index or table design wise, then there's not much we'll be able to do with this.
The best I could offer atm is to make sure your tables are not fragmented and stats updated with fullscan.
August 11, 2011 at 1:44 pm
Another important thing to note, you are using select * in your view. This will hold the current columns at the time you build the view. It will NOT be dynamic and update the columns in your view if the underlying table structure changes.
I second Remi's comment about nolock, given that this looks like a financial application I will second my own comment about not doing that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2011 at 1:50 pm
:w00t: fitting OP name and thread icon to describe the use of nolock 😀
August 11, 2011 at 2:26 pm
Wouldn't the WHERE clause be equal to
WHERE
fbt.cust_no != '0306A'
OR channel_cd in ('Correspond','Correspondent','Retail','Wholesale','IGNORE CHANNEL') ? :unsure:
August 12, 2011 at 5:43 am
sqlmaverick (8/11/2011)
I don't know how to attach the code separately.
Putting the code in a code block is relatively easy. When you post, look to the left of the text box as you're writing. You'll see IFCode Shortcuts listed. Look for "code" in square brackets. You don't really have to use the ="whatever" in the initial tag. Just code in square brackets and then code with a back slash in front of it as the closing tag (in square brackets again).
Also, when you post to a thread, there is an "edit attachments" button if you scroll far down and look on the right side of the screen. It will allow you to browse your pc and pick a file name (read the permitted file types list first) to attach to any post you make.
Hope that helps with your non-sql problem.
August 12, 2011 at 4:01 pm
I'm trying to figure out your where clause. Problem is, I don't have access to your data. What would really help (besides sample data that is representative of your problem domain) would be a logic table that shows the different outcomes based on the different inputs that could occer in your where clause (shown again below).
where
isnull(channel_cd,' ') = case when fbt.cust_no != '0306A' THEN isnull(channel_cd,' ')
when isnull(channel_cd,' ') in (
'Correspond',
'Correspondent',
'Retail',
'Wholesale')
then isnull(channel_cd,' ')
ELSE 'IGNORE CHANNEL'
end
;
August 15, 2011 at 1:32 am
Can you post an execution plan? I'd be inclined to say the first port of call should be your Where clause. Using ISNULL and case statements can cause index scans to be used as opposed to seeks, this depends on a number of other factors like fragmentation, stats/selectivity of the index being used etc
If redesigning is not an option then we'd need to see a little more info in order to determine if there's any useful indexes at all on the tables.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
August 15, 2011 at 5:16 am
LutzM (8/11/2011)
Wouldn't the WHERE clause be equal to
WHEREfbt.cust_no != '0306A'
OR channel_cd in ('Correspond','Correspondent','Retail','Wholesale','IGNORE CHANNEL') ? :unsure:
Yes, and if there's an index on channel_cd, then your version would of course be faster, Lutz. Natch 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2011 at 7:59 am
best is to change channel_cd to be NOT NULL with a default of ''. Then remove all those horrid ISNULL(.., '') things from your query. And if channel_cd is specific, a nonclustered index should get you a spiffy query.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 16, 2011 at 1:00 pm
I second ChrisTaylor's recommendation of first obtaining an execution plan. That will do wonders in understanding "why" your code is non-performant and where your roadblocks are. If you can post that back, it will open up more possibilities for recommendations IMO.
And if an execution plan is new to you, I would really recommend a read of Grant Fritchey's "Dissecting SQL Server Execution Plans" (Google it).
-Patrick
Patrick Purviance, MCDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply