5 clients takes 5 times more

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Rui Dias VD wrote:

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Sorry, forgot to tell:

I'm using SQL Server 2005 and Visual Studio 2005

|||

You should never write this type of loop. If you want to map one Mesas element to one row, please use the nodes() method as in:

SELECT

d.value('(CodMesa)[1]','integer') Chave,

d.value('(Designacao)[1]','varchar(255)') Designacao,

d.query('.') RegistoXML

FROM @.Documento.nodes('/ROOT/Mesas') as N(d)

(also please try to avoid // if you know the path).

Best regards

Michael