VIEW is Four Times Slower than SELECT? Help!

Results 1 to 2 of 2

Thread: VIEW is Four Times Slower than SELECT? Help!

  1. #1
    John Rose Guest

    Default VIEW is Four Times Slower than SELECT? Help!

    Here&#039s an interesting problem. I&#039ve never seen anything like this.<BR><BR>I have an ASP page that uses a big old nasty SELECT statement (it joins 12 tables). I created a VIEW on the server using this statement so that my ASP code would be cleaner and so that I&#039d be able to re-use the VIEW elsewhere.<BR><BR>The problem is, the VIEW takes ~40 seconds to execute, but the SELECT statement only take 10 or 11!! How could this be?<BR><BR>I&#039ve verified this by executing the VIEW and the SELECT statement in iSQLw and SQL Query Analyzer as well. The SELECT statement is the exact same one used in the VIEW... I just cut-and-paste it into the query window and execute it directly and it take 1/4 the time of the VIEW.<BR><BR>It is Microsoft SQL 6.5 on this server. Anyone ever run into anything like this before? It doesn&#039t make any sense.<BR><BR>(I know a stored procedure would perform better than a view or select, but for reasons to complicated to explain I can&#039t really implement one here)<BR><BR>-John Rose

  2. #2
    Join Date
    Dec 1969

    Default RE: VIEW is Four Times Slower than SELECT? Help!

    Views are not always the best solution in this case. I had the same problem.. a large query where I wanted to clean up the code. I started using a view and discovered a large number of problems with it, the annoying one being any time I modified the table(s) it was accessing, the view wouldn&#039t work properly.<BR><BR>If you are using a view, there&#039s obviously at least a portion of the query that is somewhat static. I would recommend using an include file and a variable in ASP.<BR><BR>Could you go into detail about the reasons why you can&#039t use a SP? I also thought I couldn&#039t use one on my case (I had search parameters that were dynamic), but found a pretty easy way around it, and it sped up the query time immensely.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts