Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 Speeding Up SQL Server Queries		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

redbrad0
Advanced Member

USA
3725 Posts
Posted - 01 April 2002 :  16:54:27  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
I have a VERY large SQL Server database (6 gigs) that I need to run querys on. I keep getting timed out errors. I put in some indexes but that does not seem to help, so I guess I need to create some stored procedures. Here is the SQL String that I would like to convert to a stored procedure..

Select DPS_Number From IDENT Where Name='" & strFormName & "' and DOB='" & strFormDOB & "'

Can someone please tell me how to create a stored procedure for this query? I have never done a stored procedure, so as much info as you can give.

THANKS A BUNCH


Brad
Web Hosting with SQL Server @ $24.95 per month

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 01 April 2002 :  16:59:50  Show Profile  Visit HuwR's Homepage
look in 'Books online' there are some good samples.

Have you tried adding a clustered index ?
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts
Posted - 01 April 2002 :  23:22:41  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
where can i find these books online?

yes right now its a clustered index and its still getting timeout errors.

Brad
Web Hosting with SQL Server @ $24.95 per month
Go to Top of Page

Gremlin
General Help Moderator

New Zealand
7528 Posts
Posted - 02 April 2002 :  05:44:59  Show Profile  Visit Gremlin's Homepage
Books Online are usually installed when you install sqlserver. If not then try re-running sqlserver setup and go into the custom options to find and install them.

Usually they can be found in Start -> All Programs -> Microsoft SQL Server -> Books Online

www.daoc-halo.com
Go to Top of Page

cevans
Junior Member

Canada
101 Posts
Posted - 02 April 2002 :  13:13:45  Show Profile  Send cevans an ICQ Message
Books Online can also be found (not surprisingly) online, here:

http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp

This is the SQL Server 2000 version.

As for your query speed, if you run your query in Query Analyzer and turn on display of the execution plan (Query -> Show Execution Plan), you can track down what (if any) indexes are being used, and see what is taking the bulk of the time for the query to execute. This should help you revise your indexes accordingly.

Note that if you change/add an index after you compile a stored procedure, you will need to recompile the procedure for it to use the updated/new index.

Clark

Edited by - cevans on 02 April 2002 13:19:22
Go to Top of Page

redbrad0
Advanced Member

USA
3725 Posts
Posted - 02 April 2002 :  17:36:16  Show Profile  Visit redbrad0's Homepage  Send redbrad0 an AOL message
Well thanks guys, I finally figured it out and I thought I would post it so if anyone needs the same thing for anything and they look at this post it will tell them how to do it.

First this is the code I stuck in Enterprise Manager to create the stored procedure..

CREATE PROCEDURE spFindIdent @FullName nvarchar(30), @DOB nvarchar(8)
AS
SELECT DPS_NUMBER, NAM, SEX, RACE, DOB1, HGT, WGT, EYE, HAI
FROM Ident
Where NAM LIKE @FullName
AND DOB1=@DOB

GO


Then in the ASP code this is what I used to run it..

strSql = "exec spFindIdent @FullName='" & strSearchFullName & "%', @DOB='" & strSearchDOB & "'"
set rs = my_Conn.Execute (strSql)


Brad
Web Hosting with SQL Server @ $24.95 per month
Go to Top of Page

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 02 April 2002 :  17:51:02  Show Profile  Visit HuwR's Homepage
brad,

to improve it even more, use getRows to fetch your recordset into an array.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000 Version 3.4.07