HOW TO HANDLE LARGE NUMBER OF ROWS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HOW TO HANDLE LARGE NUMBER OF ROWS

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-26 : 05:56:02

I am handling MSSQL and ASP, I am using 30 columns master table for my project. The speed is okay with less than 20000 rows but when i test with 5 lac rows it take more than 3 mts to execute. Is there any technology is used to index the DB or my CPU have high configuration. Pls help.

Thank you

____________
Praba

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 05:58:14

analyse your query execution plans while running queries and add appropriate indexes.

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-26 : 07:04:34

Can you pls explan how to do that? Do you have any resources for that
I am using SQL server 8.0

____________
Praba

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 07:12:14

Select show execution plan option from menu in query analyser and run the query. You'll get execution plan tab along with results. look for table scans and what value its scanning for. Consider putting indexes on column if they are used in large number of queries for searching.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 07:20:58

Post your table structure.
Post definitions for any present index.
Post your query.

E 12°55'05.63"
N 56°04'39.26"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-26 : 07:52:26

CREATE TABLE [sffiles] (
[fileid] [int] NOT NULL ,
[userid] [int] NULL ,
[servername] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filename] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[views] [int] NULL ,
[private] [int] NULL ,
[official] [int] NULL ,
[tags] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[folderid] [int] NULL
GO

sql = "select [fileid],[filename],[views],[private],
[official],[tags],[folderid] from sffiles where [userid] = '" & session("userid") & "'"

The table has no index

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 07:55:05

quote:
Originally posted by cutepraba

sql = "select [fileid],[filename],[views],[private],
[official],[tags],[folderid] from sffiles where [userid] = '" & session("userid") & "'"

The table has no index

1) Don't use dynamic sql for this
2) No index? That can have something to do with speed...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 07:58:19

First, write a stored procedure like this

CREATE PROCEDURE dbo.uspMyFirstProcedure
(
	@userID INT
)
AS

SET NOCOUNT ON

SELECT	fileID,
	fileName,
	views,
	private,
	official,
	tags,
	folderID
FROM	sfFiles
WHERE	userID = @userID

Then you create an index on sfFiles table over userid column like this

CREATE NONCLUSTERED INDEX IX_userID ON sfFiles ON (userID)

And last, but not least, call the stored procedure from your asp code.
Do never ever user dynamically built strings to call the database.

E 12°55'05.63"
N 56°04'39.26"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-26 : 08:06:12

thank you, I will go with your suggestion and get back to the forum

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 08:31:29

For TESTING purposes, you can use

sql = "EXEC dbo.uspMyFirstProcedure " & session("userid")

in your asp code. When you are satisfied, replace this string and use the command object.

E 12°55'05.63"
N 56°04'39.26"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-27 : 04:01:51

what does it means
"replace this string and use the command object"?

____________
Praba

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-27 : 04:03:54

Tried all with your suggestionm. It taking 50 seconds to execute the query? any other suggestion.
How others handling large humber of rows? I heard that google image search handling 10 million million rows!!!!!!!!!!!!

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-27 : 09:58:13

With proper index, even a simple query as yours running on millions of records should only take a few seconds.
What exactly have you done?
50sec is s till better than 3 min.

E 12°55'05.63"
N 56°04'39.26"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-09-27 : 11:00:39

quote:
Originally posted by Peso

For TESTING purposes, you can use

sql = "EXEC dbo.uspMyFirstProcedure " & session("userid")

in your asp code. When you are satisfied, replace this string and use the command object.

"When you are satisfied, replace this string and use the command object" what its means?

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-27 : 12:49:19

use the string

sql = "EXEC dbo.uspMyFirstProcedure " & session("userid")

to execute the stored procedure and test the new index. when performance is ok, replace string-call to database with proper command object call to database to avoid sql injection.

E 12°55'05.63"
N 56°04'39.26"

khasim76
Starting Member

35 Posts

Posted - 2008-09-29 : 02:20:22

the output list in table scan gives the column indexs am i right

quote:
Originally posted by Peso

use the string

sql = "EXEC dbo.uspMyFirstProcedure " & session("userid")

to execute the stored procedure and test the new index. when performance is ok, replace string-call to database with proper command object call to database to avoid sql injection.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources