sql nulls slow: number

Showing posts with label number. Show all posts

Tuesday, March 20, 2012

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

1 BACKGROUND sa . . NULL LAZY WRITER 21688 0 06/14 17:57:59 1
2 sleeping sa . . NULL LOG WRITER 200766 0 06/14 17:57:59 2
3 BACKGROUND sa . . NULL LOCK MONITOR 1203 0 06/14 17:57:59 3
4 BACKGROUND sa . . master SIGNAL HANDLER 47 0 06/14 17:57:59 4
5 BACKGROUND sa . . master TASK MANAGER 0 849 06/14 17:57:59 5
6 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 6
7 sleeping sa . . NULL CHECKPOINT SLEEP 252156 17092 06/14 17:57:59 7
8 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 8
9 BACKGROUND sa . . master TASK MANAGER 0 1595 06/14 17:57:59 9
10 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 10
11 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 11
12 BACKGROUND sa . . master TASK MANAGER 0 0 06/14 17:57:59 12
13 BACKGROUND sa . . master TASK MANAGER 0 388 06/14 17:57:59 13
14 BACKGROUND sa . . master TASK MANAGER 0 2129 06/14 17:57:59 14
15 BACKGROUND sa . . master TASK MANAGER 0 2176 06/14 17:57:59 15
17 BACKGROUND sa . . master TASK MANAGER 0 517 06/14 17:57:59 17
51 sleeping NT AUTHORITY\SYSTEM W2K92 . msdb AWAITING COMMAND 62 37 06/14 17:58:37 SQLAgent - Generic Refresher 51
52 sleeping NT AUTHORITY\SYSTEM W2K92 . msdb AWAITING COMMAND 1192429 29 09/13 12:57:53 SQLAgent - Alert Engine 52
53 sleeping WCAS PCOFFNONGGK8881 . WCASR2 AWAITING COMMAND 61 16 09/13 11:53:43 TESS 53
54 sleeping SA PCOFFNCHANWCS81 . TEMISCS AWAITING COMMAND 578 8 09/13 12:48:39 Temis-CS 54
55 sleeping SA PCOFFNHONGHSC81 . TEMISCS AWAITING COMMAND 218 3 09/13 12:07:14 Temis-CS 55
56 sleeping SA PCOFFNCHANWCS81 . TEMISCS AWAITING COMMAND 2080 228 09/13 12:49:45 Temis-CS 56
57 sleeping TESS W2K92 . TESS AWAITING COMMAND 29175 3 09/13 12:55:11 .Net SqlClient Data Provider 57
58 sleeping W2K92\w2k92_remote W2K92 . TESS AWAITING COMMAND 63 5 09/13 12:49:03 MS SQLEM 58
59 RUNNABLE W2K92\w2k92_remote W2K92 . TESS SELECT INTO 156 9 09/13 12:58:00 SQL Query Analyzer 59
60 sleeping TESS W2K92 . TESS AWAITING COMMAND 5549 0 09/13 12:58:02 .Net SqlClient Data Provider 60
61 sleeping sa W2K92 . WCASR2 AWAITING COMMAND 9422 408 09/13 12:57:57 Microsoft(R) Windows (R) 2000 Operating System 61
62 sleeping sa W2K92 . WCASR2 AWAITING COMMAND 1781 113 09/13 12:57:57 Microsoft(R) Windows (R) 2000 Operating System 62

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

sql

ASP .NET Application is Hanging when number of user grows

Hi,

Asp.net 2.0 (VB) application with Sql server 2000 Installed in windows 2000 professional server.Only less than 10 users using this application.

The problem is sometimes the application didn't response, it's very slow to get the information and after sometimes its never responding it showing hourclass.

I used Helper class (Application Block). Since Sql Server 2000 doesn't have the MARS Function, i have manually close the connection and opening again everytime.

Any idea?. Thanks in advance.

Help Class Modified Code:

--

Code :

' If the provided connection is not open, we will open it

If connection.State <> ConnectionState.Open Then

connection.Open()

mustCloseConnection = True

Else

mustCloseConnection = False

End If

Data ObjectBase :

'The transaction will call this method and also check the connection status

Public Sub BeginTransaction()

Try

If Not (myTransaction Is Nothing) Then

'Throw New Exception(ConfigManager.ReadFromXml("msg_DataAccess_Begin_trans"))

Throw New Exception("Transaction Error in Begin")

End If

If ourConnection.State <> ConnectionState.Open Then

ourConnection.Open()

myMustClose = True

Else

myMustClose = False

End If

myTransaction = ourConnection.BeginTransaction()

Catch ex As Exception

'Throw New BRSDatabaseException(ex.Message, ex)

Throw ex

End Try ''''

End Sub

Sounds like you need to determine what the bottleneck is. It could any number of things from the network, memory, hardware.

Are there other databases on this server in use?

How many records are being returned from sql server?

Are the tables that are being hit indexed?

I'd have the sql server dba monitor usage on the server in question.

|||I don't think it is SQL Server or Asp.net Win2k Pro concurrent users is five which means you need Small Business Server.|||

Hi Anthony Martin,

Yes, In this server there are about 3 applications running.

Very few records only returning (100 records)

We set only the primary key index.

is there any problem with connection closing in the object base class?

|||

I can't really speak on whether or not the .NET code is the issue. I have more experience with database tuning.

What do the query plans look like for the queries the application is using?

Are there large table index scans?

Try running sp_who2 or select * from sys.dm_db_exec_requests to monitor system usage/performance

|||

This is the output for sp_who2 in Sql Server 2000 Database.

Sunday, February 19, 2012

Array in Store procedure

Dear all,

Sometimes I happened the require that the number of input parameters
of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
Does anybody ever used an array name as the input parameter to call
the SP?

thanks,

RobertSQL doesn't have arrays but see this article for some alternatives:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--|||Robert Song wrote:

> Dear all,
> Sometimes I happened the require that the number of input parameters
> of SP is not fixed, Can sql T-SQL handle the array (dynamic array)?
> Does anybody ever used an array name as the input parameter to call
> the SP?
> thanks,
> Robert
I had this problem a year ago and solved it using a udf named fn_Split
I found it pretty fast.
You give it a list of parameters as '1,2,3,4,5,6' and it returns a temp
table
so you can select from fn_Split() and have something like
1
2
3
4
5
6

i found a version of this udf at
http://www.umachandar.com/technical...tyFns/Main7.htm

hth

f.|||Hi David,

Thank you so much for your reply, i will read the article later on.

Cheers,

Robert Song

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Monday, February 13, 2012

Arithmetic operation failure. Is this a bug, or am I doing something wrong?

I used the following code in a function. The @.infloat is input parameter

For every other number than 16.31, 17.31, 18.31, 19.31 and 20.31 (at least those numbers I checked) the code runs ok. For those numbers the result is wrong.

What I want to do is return a ten digits number left filled with zeros, with the last two digits consitered as decimals.

What I do is multiply the given float with 100.00, and take the resulting integer

But when @.infloat is multiplied with 100.00 and @.infloat in (16.31, 17.31, 18.31, 19.31 and 20.31)

the result is (1630,1730,1830,1930,2030) instead of (1631,1731,1831,1931,2031)..

Can anyone try this ?

declare @.ingood int,

@.stuffme varchar(10),

@.infloat float, @.infl float

set @.infloat=18.31

set @.infloat=isnull(@.infloat,0)

set @.stuffme='0000000000'

set @.infl=@.infloat*(100.00)

select @.infl as infl

set @.ingood=cast(@.infl as int)

select @.ingood as ingood

set @.stuffme=stuff(@.stuffme,11-len(@.ingood),len(@.ingood),@.ingood)

select @.stuffme

Replace

set @.ingood=cast(@.infl as int)

WITH

set @.ingood=cast(@.infl as NUMeERIC (10,2))

|||Do you need high precision ? Floating point data is approximate; not all values in the data type range can be precisely represented.

See here :
select convert(float, 18.31)
Result : 18.309999999999999|||

I have tried MONEY and NUMERIC datatypes for @.infloat and @.infl from your example. They both work just fine.

Sunday, February 12, 2012

Are there such a thing as arrays in TSQL?

I'm have a stored procedure that iterates through a list of numbers and adds an item for each number (user id) some of these ids are duplicates which is fine even necessary for the first part of my query but for the last I need to ensure that no duplicates id's are passed to the stored procedure, in this case called 'spInsertForBackupNote'. My thoughts here was to do something like this:

SET @.Note_Buffer = @.UserID -- @.Note_Buffer being some kind of array?

IF @.Note_Buffer = @.UserID -- If its been added to the buffer we dont execute sp
BEGIN
Do Nothing here
END

ELSE

BEGIN
EXECUTE spInsertForBackupNote @.FK_UserID, @.FK_NoteID
END

I know this would never work because it would always be false since I just added the same userid to the buffer that I want to add. But I think you see my problem. I know it should be an easy one but my TSQL is limited. I've posted the whole sp. Hope someone can help.

CREATE PROCEDURE spInsertAssignedNotesByList
@.FK_UserIDList NVARCHAR(4000) = NULL,
@.FK_NoteIDList NVARCHAR(4000) = NULL,
@.By_Who INT,
@.UserID INT

AS
SET NOCOUNT ON

DECLARE @.Length INT
DECLARE @.Note_Length INT
DECLARE @.Note_Buffer INT

DECLARE @.FirstUserIDWord NVARCHAR(4000)
DECLARE @.FirstNoteIDWord NVARCHAR(4000)

DECLARE @.FK_UserID INT
DECLARE @.FK_NoteID INT

SELECT @.Length = DATALENGTH(@.FK_UserIDList )
SELECT @.Note_Length = DATALENGTH(@.FK_NoteIDList )

DECLARE @.TempFK_NoteIDList NVARCHAR(4000) --= NULL
DECLARE @.Temp_NoteLength INT

SET @.TempFK_NoteIDList = @.FK_NoteIDList
SET @.Temp_NoteLength = DATALENGTH(@.FK_NoteIDList )

-- IF @.Length > @.Note_Length -- If we have more users than notes

BEGIN

WHILE @.Length > 0
BEGIN

IF @.Length > 0

EXECUTE @.Length = PopFirstWord @.FK_UserIDList OUTPUT, @.FirstUserIDWord OUTPUT
SELECT @.FK_UserID = CONVERT(INT, @.FirstUserIDWord)

IF @.Length > 0
BEGIN

SET @.FK_NoteIDList = @.TempFK_NoteIDList
SET @.Note_Length = @.Temp_NoteLength

WHILE @.Note_Length > 0
BEGIN
EXECUTE @.Note_Length = PopFirstWord @.FK_NoteIDList OUTPUT, @.FirstNoteIDWord OUTPUT
SELECT @.FK_NoteID = CONVERT(INT, @.FirstNoteIDWord)

IF @.Note_Length > 0
EXECUTE spInsertAssignedNoteDetail @.FK_UserID, @.FK_NoteID

SET @.Note_Buffer = @.UserID
EXECUTE spInsertForBackupNote @.FK_UserID, @.FK_NoteID, @.By_Who, @.UserID -- NEW HERE
END
END

END
END

----------------
GOThere are not arrays in TSQL (at least the current version).

You can simulate it by placing the values into a temp table (perhaps by passing in a delimited string, and then using a user defined function that returns a table variable). Then, once you have the table, you can do what TSQL is best at, Set operations.|||can you show an example of how to display the contents of a table variable?

Thanks|||What do you mean by "display"? SQL Server runs on the server, and as such really does not expose a user interface.|||the UDF returns a table variable and I need to bind to it and display the contents on a .net web page.|||At the end of the SP,

SELECT * FROM TableVariable

Do an ExecuteReader or similar and bind the datareader to the grid, whatever.|||I am using a strongly typed dataset and am using com.executenonquery to bind to a datagrid and the results are:
I get back the columns names from the function or sp (they both do the same) but the columns are empty.

are there any MSDE T-SQL code limitations?

I am writing a database app which will run under MSDE on the user's systems.

Other than limitations as to the number of concurrent users, are there any
issues that I need to be aware of when programming the database using SQL
Server 2000? i.e., are there T-SQL programming statements that will run
under SQL Server 2000 as part of my development enviornemnt that won't run
under MSDE on the end users machines, or will MSDE handle anything that SQL
Server can from the database engine standpoint?

Thanks!hi Mike,
"Mike N." <BadDog@.thepound.com> ha scritto nel messaggio
news:r3erb.5780$942.4765@.newssvr25.news.prodigy.co m...
> I am writing a database app which will run under MSDE on the user's
systems.
> Other than limitations as to the number of concurrent users, are there any
> issues that I need to be aware of when programming the database using SQL
> Server 2000? i.e., are there T-SQL programming statements that will run
> under SQL Server 2000 as part of my development enviornemnt that won't run
> under MSDE on the end users machines, or will MSDE handle anything that
SQL
> Server can from the database engine standpoint?

all T-SQL statements SQL Server 2000 supports are supported by MSDE...
you only (only...) have database limitation size, limited replication
support, no mail support...
for further info please visit
http://www.microsoft.com/sql/msde/p...fo/features.asp

hth
Andrea Montanari (Microsoft MVP - SQL Server)
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply

Tuesday, March 20, 2012

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

ASP .NET Application is Hanging when number of user grows

Sunday, February 19, 2012

Array in Store procedure

Monday, February 13, 2012

Arithmetic operation failure. Is this a bug, or am I doing something wrong?

Sunday, February 12, 2012

Are there such a thing as arrays in TSQL?

are there any MSDE T-SQL code limitations?

sql nulls slow

Blog Archive

About Me

About Me

Followers

Blog Archive