To improve the performance of the query.

Question

To improve the performance of the query.

sqlmaverick

Default port

Points: 1462
More actions
August 11, 2011 at 1:33 pm

#243166

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

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/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

:w00t: fitting OP name and thread icon to describe the use of nolock 😀

LutzM SSC Guru Points: 107049 More actions · Answer 4

Wouldn't the WHERE clause be equal to

WHERE

fbt.cust_no != '0306A'

OR channel_cd in ('Correspond','Correspondent','Retail','Wholesale','IGNORE CHANNEL') ? :unsure:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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

;

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 7

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

LutzM (8/11/2011)
Wouldn't the WHERE clause be equal to
WHERE
fbt.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 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

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

Patrick Purviance SSC Eights! Points: 990 More actions · Answer 10

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