Count Records and TotalRecords ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Count Records and TotalRecords ??

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2009-01-18 : 07:28:57

Hello
i use below code and storedprocedure for count of records.. if records count be almost 300-400 in 1.000.000 records related to where statements, i see results so fast.. but if records count be 4000-5000 in 1.000.000 records related to where statements, i see record so slow.. sometimes it give me error that time is finished..Error line is sqlCmd.ExecuteNonQuery().. Why if records count is 4000 or 40.000, my code runs slow ? it runs for 1.000.000 records for both of condition..
My StoredProcedure is below..
ALTER PROCEDURE dbo.StoredProcedure8
(@ilanturu varchar(18),
@kategori varchar(23),
@kategori1 varchar(48),
@kategori2 varchar(55),
@garantidurumu varchar(26),
@sifirikinciel varchar(17),
@kargoucreti varchar(17),
@sehir varchar(30),
@tfiyat money,
@tfiyat1 money,
@fiyat money,
@fiyat1 money,
@aktifilan bit,
@TotalRecords int output)

/*
(@TotalRecords int output)

@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT ON

SET Statistics io ON

/* SET NOCOUNT ON */
SELECT @TotalRecords = Count ([urunnumarasi])
FROM urunlistesi
Where ((@ilanturu is null) or (ilanturu = @ilanturu))
and ((@kategori is null) or (kategori = @kategori))
and ((@kategori1 is null) or (kategori1 = @kategori1))
and ((@kategori2 is null) or (kategori2 = @kategori2))
and ((@garantidurumu is null) or (garantidurumu=@garantidurumu))
and ((@sifirikinciel is null) or (sifirikinciel=@sifirikinciel))
and ((@kargoucreti is null) or (kargoucreti=@kargoucreti))
and ((@sehir is null) or (sehir=@sehir))
and ((@tfiyat is null) or (tfiyat>=@tfiyat))
and ((@tfiyat1 is null) or (tfiyat<=@tfiyat1))
and ((@fiyat is null) or (fiyat>=@fiyat))
and ((@fiyat1 is null) or (fiyat<=@fiyat1))
and ((@aktifilan is null) or (aktifilan = @aktifilan))

RETURN

my page.aspx is below..
Dim conn As String = ConfigurationManager.ConnectionStrings("urunlerCS").ConnectionString
Dim mySqlConn As New SqlConnection(conn)
Dim TotalRowCount As Integer
Dim sqlCmd As SqlCommand = New SqlCommand
Dim paramTotalEvent As SqlParameter = New SqlParameter

sqlCmd.Connection = mySqlConn
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.CommandText = "StoredProcedure8"

Dim ccid1 As SqlParameter = sqlCmd.Parameters.Add("@ilanturu", SqlDbType.VarChar, 18)
ccid1.Direction = ParameterDirection.Input
If ilanturu.Text = "Tüm Ilanlar" Then
ccid1.Value = DBNull.Value
Else : ccid1.Value = ilanturu.Text
End If
Dim ccid22 As SqlParameter = sqlCmd.Parameters.Add("@kategori", SqlDbType.VarChar, 23)
ccid22.Direction = ParameterDirection.Input
ccid22.Value = aramal1.Text

Dim ccid2 As SqlParameter = sqlCmd.Parameters.Add("@kategori1", SqlDbType.VarChar, 48)
ccid2.Direction = ParameterDirection.Input
If aramal2.Text = "" Then
ccid2.Value = DBNull.Value
Else : ccid2.Value = aramal2.Text
End If

Dim ccid3 As SqlParameter = sqlCmd.Parameters.Add("@kategori2", SqlDbType.VarChar, 55)
ccid3.Direction = ParameterDirection.Input
If aramal3.Text = "" Then
ccid3.Value = DBNull.Value
Else : ccid3.Value = aramal3.Text
End If

Dim ccid4 As SqlParameter = sqlCmd.Parameters.Add("@garantidurumu", SqlDbType.VarChar, 26)
ccid4.Direction = ParameterDirection.Input
If GarantidurumuDDL.Text = "Hepsini Göster" Then
ccid4.Value = DBNull.Value
Else : ccid4.Value = GarantidurumuDDL.Text
End If
Dim ccid5 As SqlParameter = sqlCmd.Parameters.Add("@sifirikinciel", SqlDbType.VarChar, 17)
ccid5.Direction = ParameterDirection.Input
If urundlDDL.Text = "Hepsini Göster" Then
ccid5.Value = DBNull.Value
Else : ccid5.Value = urundlDDL.Text
End If
Dim ccid6 As SqlParameter = sqlCmd.Parameters.Add("@kargoucreti", SqlDbType.VarChar, 12)
ccid6.Direction = ParameterDirection.Input
If kargoucretiDDL.Text = "Hepsini Göster" Then
ccid6.Value = DBNull.Value
Else : ccid6.Value = kargoucretiDDL.Text
End If
Dim ccid7 As SqlParameter = sqlCmd.Parameters.Add("@sehir", SqlDbType.VarChar, 30)
ccid7.Direction = ParameterDirection.Input
If sehirDDL.Text = "Hepsini Göster" Then
ccid7.Value = DBNull.Value
Else : ccid7.Value = sehirDDL.Text
End If

Dim ccid8 As SqlParameter = sqlCmd.Parameters.Add("@tfiyat", SqlDbType.Decimal)
ccid8.Direction = ParameterDirection.Input
If pfiyatltb.Text = "" Then
ccid8.Value = DBNull.Value
Else : ccid8.Value = Decimal.Parse(pfiyatltb.Text)
End If

Dim ccid9 As SqlParameter = sqlCmd.Parameters.Add("@tfiyat1", SqlDbType.Decimal)
ccid9.Direction = ParameterDirection.Input
If pfiyatltb0.Text = "" Then
ccid9.Value = DBNull.Value
Else : ccid9.Value = Decimal.Parse(pfiyatltb0.Text)
End If

Dim ccid10 As SqlParameter = sqlCmd.Parameters.Add("@fiyat", SqlDbType.Decimal)
ccid10.Direction = ParameterDirection.Input
If fiyatltb.Text = "" Then
ccid10.Value = DBNull.Value
Else : ccid10.Value = Decimal.Parse(fiyatltb.Text)
End If

Dim ccid11 As SqlParameter = sqlCmd.Parameters.Add("@fiyat1", SqlDbType.Decimal)
ccid11.Direction = ParameterDirection.Input
If fiyatltb0.Text = "" Then
ccid11.Value = DBNull.Value
Else : ccid11.Value = Decimal.Parse(fiyatltb0.Text)
End If
Dim ccid12 As SqlParameter = sqlCmd.Parameters.Add("@aktifilan", SqlDbType.Bit)
ccid12.Direction = ParameterDirection.Input
ccid12.Value = "True"

paramTotalEvent.ParameterName = "@TotalRecords"
paramTotalEvent.Direction = ParameterDirection.Output
paramTotalEvent.DbType = DbType.Int16
paramTotalEvent.Size = 50
sqlCmd.Parameters.Add(paramTotalEvent)
sqlCmd.Connection.Open()
sqlCmd.ExecuteNonQuery()
sqlCmd.Connection.Close()

TotalRowCount = sqlCmd.Parameters("@TotalRecords").Value

ilansayisiL.Text = TotalRowCount.ToString

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources