Optimize a SQL view?

[charanch]

Greetings, I am using a sql view to extract a list based on a zip code input. This works but it's slow because of the enormous amount of records the query has to look through. Is there a better way to do this and if so, how? I am not familiar with stored procedures or how to write one or call one. Here's my query:

if request.form("zip")>"" then
set rs=objConn.execute ("select * from Schedule where cadZip='" & request.form("zip") & "'")
if not rs.eof then
response.write rs("prgName")
end if
end if

Any thoughts would be greatly appreciated. Many thanks in advance.

 

[Sheco]

Did you write the View?

If it is a View written by someone else for some other purpose then maybe it is slow because it is getting field that you ultimately don't need.

What I mean is, perhaps if you can get a copy of the view's SQL statement you could simplify it to only return the prgName field that you are seeking and then perhaps it would run faster.

 

[charanch]

Hi Sheco,

Thanks for the reply. I did not write the view but I didn't want to include the whole farm in my post because it's long. It returns about 8 fields, not just the program name, but the sub-title, location, date of the program, discount available, etc. It is displayed in an html table where people can choose and register. The view is made up of 4 tables... Since we use that particular view for many things because it groups and it actually works I would like to stick with it. I can use that view in a stored procedure, right? I've already started fumbling around on my own... Thanks again.

 

[gmmastros]

Sheco makes a good point about optimizing the query. I would just like to add a couple thoughts.

First, your query basically looks like...

Select * from Schedule Where cadZip='12345'

But in the code, you are only using prgName. I suggest you modify the query to be...

Select prgName from Schedule Where cadZip='12345'

By using 'Select *', you are returning all the fields from table. Since you are only using 1 field, you would be better off only returning that field.

Second, I suggest you look at the indexes that exist on the Schedule table. Is cadZip indexed. If it's not, and there are many records in this table, you would get better performance by creating an index.

Third, do a google search on "SQL Injection Attack". Read the first couple of pages it returns.

Good Luck

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[charanch]

I optimized the query like ya'll suggested and it IS running faster now. Many thanks for your help!