Microsoft SQL View Slower than Dynamic access via JDBC

Hi Experts
We have a program that access MS SQL via JDBC. When originally written there sql statment went dirrectly to the table and had a lot of fixed And conditions.
Example
where (doc Like"A%" or Doc Like 'B%' or DocLike'%C%')
I created a view that that erflects the above logic on the sql database
The question is why does the query over the view take a log longer than the query of teh table whith the dynalic slection and can I make the view quicker.

Cheers

Dave
LVL 14
daveslaterAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Are you saying the SELECT statement from JDBC is dynamically created based on the criteria selected and therefore different to the VIEW?  if so that would explain the difference.

Also how are you handling the parameters in the VIEW?
0
 
subhashpuniaCommented:
Are you using the view with schema name?
0
 
raulggonzalezCommented:
A view is exactly the same as the query which contains, so executing the query from the view and
SELECT * FROM view

should have the same time of response.

You can enhance your view's performance creating a clustered index

http://msdn.microsoft.com/en-en/library/ms188783.aspx

Good luck
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
daveslaterAuthor Commented:
Hi sorry for the delay
the dynamic code is set as follows


 stmtv = 'Select arc_doctitle,'                  
 +' arc_docsubject, arc_docdesc2,arc_docname'    
 + ', arc_docdate1 from cpArchiveIndex a' ;      
   stmtv += '  where year(a.arc_docdate1) = ?'  
   +        '  and   month(a.arc_docdate1) = ?'  
   +        '  and   day(a.arc_docdate1) = ?' ;  

stmtv +=                                        
' or ((A.DOCDESC2 LIKE ''N/B%'') ' +            
' or  (A.DOCDESC2  LIKE ''%Quotation%'') ' +    
' or  (A.DOCDESC2  LIKE ''     over%'')  ' +    
' or  (A.DOCDESC2  LIKE ''%G/Enquiry%'') ' +    
' or  (A.DOCDESC2  LIKE ''Cancellation%'')' +    
' or  (A.DOCDESC2  LIKE ''%On Cover%'') )' +    
' and (A.DOCDESC2<> ''N/B upgrade'') ' ;        


JDBC_SetInt(stmt:1:SearchYY);  
JDBC_SetInt(stmt:2:SearchMM);  
JDBC_SetInt(stmt:3:SearchDD);  
0
 
raulggonzalezCommented:
Hi,

I guess you cannot get rid of LIKE '%patterns%'

but try to modify

stmtv += '  where year(a.arc_docdate1) = ?'  
   +        '  and   month(a.arc_docdate1) = ?'  
   +        '  and   day(a.arc_docdate1) = ?' ;  

and compare it like

where a.arc_docdate1 = ?

this way, if you have any index created on the column, will be used for index seek.

Good luck.
0
 
daveslaterAuthor Commented:
Hi raulggonzalez

I have already optimized the query to do that and it is several seconds faster.

I am just curious why a dynamic statement is faster than a view.

Dave
0
 
raulggonzalezCommented:
Me too, because it shouldn't be...

A view is just a select (not indexed views) so it really shouldn't be faster or slower than the query which represents...

http://en.wikipedia.org/wiki/View_(database)

Please correct me if I'm wrong.

Cheers.
0
 
raulggonzalezCommented:
Hi,

the only thing my mind can figure out is that for some reason the execution plans are different using the select and the view. Have you checked how they are?

Cheers.
0
 
daveslaterAuthor Commented:
how do i check the execution plan?

dave
0
 
raulggonzalezCommented:
Hi,

In SQL SERVER Management Studio you have the possibility of including the Actual Execution Plan, it's one of the buttons close to Execute.

When you execute the query, you will have a third tab 'Execution Plan'

You can have quick reference here...

http://www.simple-talk.com/sql/performance/execution-plan-basics/


Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.