Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/d.../>
ll_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/d...
tall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?
Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/de...stall_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:

> Bruce Thomas wrote:
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/de...stall_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Developer Version

I have been trying to determine why an stored proc that I wrote that makes a
single random selection from a list of about 3 million phone numbers works
slowly in a particular client's office when in use by multiple Clients.
During investigation I found that the IT entrepreneur who supplied and
installed my Client's software provided a Developer's edition of SLQ Server.
As it is illegal to use this Edition commercially, my Client will be
upgrading to an appropriate version before the app goes into commercial use.
Meanwhile I would like to know if the Developer's Edition includes a
concurrent workload governor like the the Personal Edition or any other kind
of governor that would result in performance degradation?Bruce Thomas wrote:
> I have been trying to determine why an stored proc that I wrote that
> makes a single random selection from a list of about 3 million phone
> numbers works slowly in a particular client's office when in use by
> multiple Clients. During investigation I found that the IT
> entrepreneur who supplied and installed my Client's software provided
> a Developer's edition of SLQ Server. As it is illegal to use this
> Edition commercially, my Client will be upgrading to an appropriate
> version before the app goes into commercial use. Meanwhile I would
> like to know if the Developer's Edition includes a concurrent
> workload governor like the the Personal Edition or any other kind of
> governor that would result in performance degradation?
No. You can't use Developer Edition in a production environment. But for
testing it's fine. There is no workload governor. Same as enterprise
edition.
To upgrade an edition of SQL Server:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
How are you performing a single random selection from a 3 million row
table? Are you scanning the entire table or using some other means?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for info about governor. I am going to re-write the application so
that it grabs 1000 rows (which is enough for a user for a day) and then uses
them from a disconnected dataset in the front end, synchronising back
periodically. The stored proc now picks 1000 rows in 3 secs as compared to
0.7 secs for 1 row. I'm just worried about what will happen if 30 users log
on at about the same time.
The selection of rows is not really random, just somewhat random. When the
rows are entered they are given an index ID which is the reverse of the last
6 digits of the phone number. The selection is as follows where SPID =Unique
Sales person ID and is varchar(4):-
UPDATE tbTelNums SET SPID=@.SPID WHERE TelNumID IN (SELECT TOP 1000 TelNumID
FROM tbTelNums WHERE SPID IS NULL ORDER BY CallCount, RevIndx). The proc
then returns details for the marked rows. When I was returning one row I
used output parameters but this will now be a select query such as SELECT *
FROM tbTelNums WHERE SPID=@.SPID.
I plan to use a separate stored proc for UPDATE to synchronise the dataset.
ADDing and DELETEing only take place in a separate Admin front end.
Please let me know if you feel the stored proc for selecting rows could be
improved.
"David Gugick" wrote:
> Bruce Thomas wrote:
> > I have been trying to determine why an stored proc that I wrote that
> > makes a single random selection from a list of about 3 million phone
> > numbers works slowly in a particular client's office when in use by
> > multiple Clients. During investigation I found that the IT
> > entrepreneur who supplied and installed my Client's software provided
> > a Developer's edition of SLQ Server. As it is illegal to use this
> > Edition commercially, my Client will be upgrading to an appropriate
> > version before the app goes into commercial use. Meanwhile I would
> > like to know if the Developer's Edition includes a concurrent
> > workload governor like the the Personal Edition or any other kind of
> > governor that would result in performance degradation?
> No. You can't use Developer Edition in a production environment. But for
> testing it's fine. There is no workload governor. Same as enterprise
> edition.
> To upgrade an edition of SQL Server:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_install_1d7y.asp
> How are you performing a single random selection from a 3 million row
> table? Are you scanning the entire table or using some other means?
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>