I have what is probably an elementary question, and will be grateful for you
r
help. I am just moving a database from Jet and learning SQL Server as I go.
In Jet, if I had a query doing aggregate functions on a one-many join, I
would often use First() and Last() on fields on the one side other than the
unique identifier, for efficiency. E.g., I would group on the one side by
Student ID, and refer to First([Student name]). This seems more efficient
than "GROUP BY [student name]", which would be redundant since I am already
grouping by Student ID; and more computationally efficient than Min() or
Max().
Question is, what's the efficient way to do this in SQL Server, since
First() and Last() are not available (which I understand is because SQL
Server more strictly adheres to the paradigm of the unordered set)? Thanks.
--DavidDavid
First() --SQL Server is MIN()
Last() --SQL Server is MAX()
"David Roodman" <DavidRoodman@.discussions.microsoft.com> wrote in message
news:C1F7D9F9-D540-421A-9943-C07B4FC6295C@.microsoft.com...
>I have what is probably an elementary question, and will be grateful for
>your
> help. I am just moving a database from Jet and learning SQL Server as I
> go.
> In Jet, if I had a query doing aggregate functions on a one-many join, I
> would often use First() and Last() on fields on the one side other than
> the
> unique identifier, for efficiency. E.g., I would group on the one side by
> Student ID, and refer to First([Student name]). This seems more efficient
> than "GROUP BY [student name]", which would be redundant since I am
> already
> grouping by Student ID; and more computationally efficient than Min() or
> Max().
> Question is, what's the efficient way to do this in SQL Server, since
> First() and Last() are not available (which I understand is because SQL
> Server more strictly adheres to the paradigm of the unordered set)?
> Thanks.
> --David
Showing posts with label efficiently. Show all posts
Showing posts with label efficiently. Show all posts
Sunday, February 19, 2012
Efficiently refering to non-identifier fields on "one" side of
Whether this helps me, as Bill Clinton said, depends on what the meaning of
"is" is. I understand that I can substite, as you seem to be suggesting by
"is." My question is whether this is *efficient*. If I join the student tabl
e
to the attendance record table, which has one row for each student and schoo
l
day, and use Min(), then wouldn't I be telling SQL server to look, for each
student, at 180 identical copies of that student's name and find the
"minimum" one? Wouldn't that be inefficient?
--David
"Uri Dimant" wrote:
> David
> First() --SQL Server is MIN()
> Last() --SQL Server is MAX()
>
> "David Roodman" <DavidRoodman@.discussions.microsoft.com> wrote in message
> news:C1F7D9F9-D540-421A-9943-C07B4FC6295C@.microsoft.com...
>
>Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
model properly normalised?
ML
http://milambda.blogspot.com/|||I think it is properly normalized... I have one table with a row for each
student. It contains student ID, name, and so on. I have another table with
a
row for each student and day, for attendance. The two are linked by student
ID. I want a view that gives me each student's name and the number of days
present. Actually, this is a made-up example, but should serve. I had though
t
the efficient way to do this in Jet was to GROUP BY the student ID, and
report the First() of the student name,and some aggregate statistics on the
student's attendance. I avoided grouping by student name or applying min() t
o
the student name because I thought those options were both more
computationally expensive. Is this untrue? Thank you.
"ML" wrote:
> Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
> model properly normalised?
>
> ML
> --
> http://milambda.blogspot.com/|||To amplify my previous explanation:
It says "For greater speed, use Group By on as few fields as possible. As an
alternative, use the First function where appropriate."
at
http://msdn.microsoft.com/library/d...ter
S.asp|||> To amplify my previous explanation:
> It says "For greater speed, use Group By on as few fields as possible. As
> an
> alternative, use the First function where appropriate."
There is no FIRST() in SQL Server, so what MSDN happens to say about Jet is
completely irrelevant here.
Are you using SQL Server or Jet?|||>I think it is properly normalized... I have one table with a row for each
> student. It contains student ID, name, and so on. I have another table
> with a
> row for each student and day, for attendance. The two are linked by
> student
> ID. I want a view that gives me each student's name and the number of days
> present. Actually, this is a made-up example, but should serve. I had
> thought
> the efficient way to do this in Jet
Jet? What are you doing in a SQL Server group?
Anyway, you don't have any DDL, so I'll make some up for you.
CREATE TABLE dbo.Students
(
StudentID INT PRIMARY KEY, -- generated from where?
FullName VARCHAR(32)
)
GO
CREATE TABLE dbo.StudentAttendance
(
StudentID INT FOREIGN KEY
REFERENCES dbo.Students(StudentID),
dt SMALLDATETIME,
Present INT NOT NULL DEFAULT 1
)
GO
SET NOCOUNT ON;
INSERT dbo.Students SELECT 1, 'Johnny Keener';
INSERT dbo.Students SELECT 2, 'Molly NiceGirl';
INSERT dbo.Students SELECT 3, 'Trish WithSTD';
INSERT dbo.Students SELECT 4, 'Jimmy BadApple';
INSERT dbo.Students SELECT 5, 'New Kid';
GO
INSERT dbo.StudentAttendance SELECT 1, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 3, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060103', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060102', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060103', 0;
GO
SELECT s.StudentID,
s.FullName,
DaysPresent = COALESCE(a.DaysPresent, 0)
FROM
dbo.Students s
LEFT OUTER JOIN
(
SELECT StudentID,
DaysPresent = SUM(Present)
FROM dbo.StudentAttendance
GROUP BY StudentID
) a
ON
s.StudentID = a.StudentID;
GO
DROP TABLE dbo.StudentAttendance;
DROP TABLE dbo.Students;
I don't see why FIRST() or LAST() would make sense in any of this, even in
Jet.
A|||From my first post: "I am just moving a database from Jet and learning SQL
Server as I go."
Am I not being clear? It seems that people are reading too fast but trying
to be helpful. I was doing what is recommended practice in Jet; the
underlying concern that motivated that advice from MSDN still stands,
seemingly; and I am wondering what is the efficient way to handle it in SQL
Server. "Efficient" for the computer, not me.
"is" is. I understand that I can substite, as you seem to be suggesting by
"is." My question is whether this is *efficient*. If I join the student tabl
e
to the attendance record table, which has one row for each student and schoo
l
day, and use Min(), then wouldn't I be telling SQL server to look, for each
student, at 180 identical copies of that student's name and find the
"minimum" one? Wouldn't that be inefficient?
--David
"Uri Dimant" wrote:
> David
> First() --SQL Server is MIN()
> Last() --SQL Server is MAX()
>
> "David Roodman" <DavidRoodman@.discussions.microsoft.com> wrote in message
> news:C1F7D9F9-D540-421A-9943-C07B4FC6295C@.microsoft.com...
>
>Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
model properly normalised?
ML
http://milambda.blogspot.com/|||I think it is properly normalized... I have one table with a row for each
student. It contains student ID, name, and so on. I have another table with
a
row for each student and day, for attendance. The two are linked by student
ID. I want a view that gives me each student's name and the number of days
present. Actually, this is a made-up example, but should serve. I had though
t
the efficient way to do this in Jet was to GROUP BY the student ID, and
report the First() of the student name,and some aggregate statistics on the
student's attendance. I avoided grouping by student name or applying min() t
o
the student name because I thought those options were both more
computationally expensive. Is this untrue? Thank you.
"ML" wrote:
> Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
> model properly normalised?
>
> ML
> --
> http://milambda.blogspot.com/|||To amplify my previous explanation:
It says "For greater speed, use Group By on as few fields as possible. As an
alternative, use the First function where appropriate."
at
http://msdn.microsoft.com/library/d...ter
S.asp|||> To amplify my previous explanation:
> It says "For greater speed, use Group By on as few fields as possible. As
> an
> alternative, use the First function where appropriate."
There is no FIRST() in SQL Server, so what MSDN happens to say about Jet is
completely irrelevant here.
Are you using SQL Server or Jet?|||>I think it is properly normalized... I have one table with a row for each
> student. It contains student ID, name, and so on. I have another table
> with a
> row for each student and day, for attendance. The two are linked by
> student
> ID. I want a view that gives me each student's name and the number of days
> present. Actually, this is a made-up example, but should serve. I had
> thought
> the efficient way to do this in Jet
Jet? What are you doing in a SQL Server group?
Anyway, you don't have any DDL, so I'll make some up for you.
CREATE TABLE dbo.Students
(
StudentID INT PRIMARY KEY, -- generated from where?
FullName VARCHAR(32)
)
GO
CREATE TABLE dbo.StudentAttendance
(
StudentID INT FOREIGN KEY
REFERENCES dbo.Students(StudentID),
dt SMALLDATETIME,
Present INT NOT NULL DEFAULT 1
)
GO
SET NOCOUNT ON;
INSERT dbo.Students SELECT 1, 'Johnny Keener';
INSERT dbo.Students SELECT 2, 'Molly NiceGirl';
INSERT dbo.Students SELECT 3, 'Trish WithSTD';
INSERT dbo.Students SELECT 4, 'Jimmy BadApple';
INSERT dbo.Students SELECT 5, 'New Kid';
GO
INSERT dbo.StudentAttendance SELECT 1, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 3, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060103', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060102', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060103', 0;
GO
SELECT s.StudentID,
s.FullName,
DaysPresent = COALESCE(a.DaysPresent, 0)
FROM
dbo.Students s
LEFT OUTER JOIN
(
SELECT StudentID,
DaysPresent = SUM(Present)
FROM dbo.StudentAttendance
GROUP BY StudentID
) a
ON
s.StudentID = a.StudentID;
GO
DROP TABLE dbo.StudentAttendance;
DROP TABLE dbo.Students;
I don't see why FIRST() or LAST() would make sense in any of this, even in
Jet.
A|||From my first post: "I am just moving a database from Jet and learning SQL
Server as I go."
Am I not being clear? It seems that people are reading too fast but trying
to be helpful. I was doing what is recommended practice in Jet; the
underlying concern that motivated that advice from MSDN still stands,
seemingly; and I am wondering what is the efficient way to handle it in SQL
Server. "Efficient" for the computer, not me.
Efficiently joining same table twice
My main table has the following structure:
t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]
I want to join this table with the following second table:
t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),
(NULL,1,"value2")]
The join should first try to find a match on id_primary and only if that
fails it should find a match on id_secundary. Every row in t1 is matched
against a single row in t2.
The following query works:
select
a.name, isnull(b.value, c.value)
from
t1 a left outer join t2 b on a.id_primary = b.id_primary
left outer join t2 c on a.id_secundary = c.id_secundary
I'm wondering though if it would be possible to write a query that only uses
t2 once, since it actualy is quite a complex query that is calculated twice
now. Any ideas (besides using a temp table)?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||On 26 Feb 2005 06:56:56 -0800, "--CELKO--" <jcelko212@.earthlink.net>
wrote:
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications.
CREATE TABLE t1 (
[id_primary] [int] NOT NULL ,
[id_secundary] [int] NOT NULL ,
[name] [char] (10) NOT NULL
)
GO
CREATE TABLE t2 (
[id_primary] [int] NULL ,
[id_secundary] [int] NULL ,
[value] [char] (10) NOT NULL
)
GO
INSERT INTO t1 VALUES (1,3,'Name1')
GO
INSERT INTO t1 VALUES (2,3,'Name2')
GO
INSERT INTO t2 VALUES (1,NULL,'Value1')
GO
INSERT INTO t2 VALUES (NULL,3,'Value2')
GO
The result of the join should be the following:
name
---- ----
Name1 Value1
Name2 Value2
The first row in t1 ('Name1') will find a match on the id_primary
column (id_primary=1) in t2.
The second row in t1 ('Name2') will not find a match on id_primary (2)
in t2, but will find a match on id_secundary (3) in t2|||honda (hondass50@.hotmail.com) writes:
> I'm wondering though if it would be possible to write a query that only
> uses t2 once, since it actualy is quite a complex query that is
> calculated twice now. Any ideas (besides using a temp table)?
I was trying to achieve something, but I could not get it to work. In
any case, it is far from certain that it would have been more effecient
that your current query, which appears to be best way to write it anyway.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Efficiently Inserting 1 Million records
I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:
> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:
> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan
efficiently creating random numbers in very large table
Hello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rodselect TOP 5 PERCENT * from [YourTable] order by newid()|||select TOP 5 PERCENT * from [YourTable] order by newid()
Thank you, I'll give that a go.
Regards,
Rod|||that won't populate your table with any random numbers obviously.
it will give you a random 5% slice of the table. a different slice each time you run it.|||Thanks, Good point; maybe I can have another column to set a bit , so that I can reproduce. I'll have to test performance, perhaps someone has some experience with this or have a different technique to propose. Thank you.
Rod|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.
That's ok blindman, I just neede something that's efficient in terms populating random values. Regards, Rod|||just create a GUID column with a default of NEWID()
Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.|||Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.
...ugh.. Thanks. There does not seem to be a really efficient way of doing this...
Thanks for you input. Rod|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end
That maybe ok, you're right, not great but maybe we can live that. Thanks for your code.
Regards,
Rod
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rodselect TOP 5 PERCENT * from [YourTable] order by newid()|||select TOP 5 PERCENT * from [YourTable] order by newid()
Thank you, I'll give that a go.
Regards,
Rod|||that won't populate your table with any random numbers obviously.
it will give you a random 5% slice of the table. a different slice each time you run it.|||Thanks, Good point; maybe I can have another column to set a bit , so that I can reproduce. I'll have to test performance, perhaps someone has some experience with this or have a different technique to propose. Thank you.
Rod|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.|||If you really want a column of random values, then just create a GUID column with a default of NEWID(). But this won't give you a random sample every time, of course.
That's ok blindman, I just neede something that's efficient in terms populating random values. Regards, Rod|||just create a GUID column with a default of NEWID()
Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.|||Ofcourse this works but if your table is really that big beware of the time it takes to alter the table! SQL Server has to expand each record so numerous page splits will occur, indexes will have to be rebuild, etc, etc. This could take a couple of hours.
...ugh.. Thanks. There does not seem to be a really efficient way of doing this...
Thanks for you input. Rod|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end|||how many rows is the table?
also, you can generate random numbers in sql using rand() if you don't like guids. if a random number from 0-255 is sufficient you could store it in a tinyint and less page splits would result.
this code ran in 31 sec on my dev box. not great, but it is what it is:
set nocount on
declare @.t table (RandomColumn tinyint)
declare @.i int
set @.i=0
while @.i < 1000000
begin
insert into @.t select round(rand() * 255, 0)
set @.i = @.i + 1
end
That maybe ok, you're right, not great but maybe we can live that. Thanks for your code.
Regards,
Rod
efficient SQL profiling
Hi,
Is there a way to efficiently monitor SQL performance using SQL Profiler,
and my meaning is to see distinct SQL query sent to server (by ignoring
parameter values sent with the queries)
this mean that query like
select * from users where user_id = 10
and
select * from users where user_id = 20
will be shown as the same query counted 2 times, and this way see which
query is performed mostly on the server, and priorities it's performance
tuning.
creative suggestions invited.
thanx.If it is simply the where clause that you are trying to eliminate, then why
not log all your queries to a SQL table and then do a select distinct from
the table, and get the substring of the query that does not include the
where clause?
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there a way to efficiently monitor SQL performance using SQL Profiler,
> and my meaning is to see distinct SQL query sent to server (by ignoring
> parameter values sent with the queries)
> this mean that query like
> select * from users where user_id = 10
> and
> select * from users where user_id = 20
> will be shown as the same query counted 2 times, and this way see which
> query is performed mostly on the server, and priorities it's performance
> tuning.
> creative suggestions invited.
> thanx.
>
>|||Thanx,
2 points:
1. how do i log all my queries to the database in an encapsulated way?
1.1 can i also log this way the time it took to execute?
2. my buttleneck might also be a execute statement - well, this will go also
with your suggestion, just truncated before the starting '('.
"Aaron Relph" <x@.x.com> wrote in message
news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> If it is simply the where clause that you are trying to eliminate, then
why
> not log all your queries to a SQL table and then do a select distinct from
> the table, and get the substring of the query that does not include the
> where clause?
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
Profiler,
>|||This may help -
http://www.sql-server-performance.c...ofiler_tips.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:OQuACAi%23DHA.3292@.TK2MSFTNGP11.phx.gbl...
> Thanx,
> 2 points:
> 1. how do i log all my queries to the database in an encapsulated way?
> 1.1 can i also log this way the time it took to execute?
> 2. my buttleneck might also be a execute statement - well, this will go
also
> with your suggestion, just truncated before the starting '('.
>
>
> "Aaron Relph" <x@.x.com> wrote in message
> news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> why
from
> Profiler,
ignoring
which
performance
>
Is there a way to efficiently monitor SQL performance using SQL Profiler,
and my meaning is to see distinct SQL query sent to server (by ignoring
parameter values sent with the queries)
this mean that query like
select * from users where user_id = 10
and
select * from users where user_id = 20
will be shown as the same query counted 2 times, and this way see which
query is performed mostly on the server, and priorities it's performance
tuning.
creative suggestions invited.
thanx.If it is simply the where clause that you are trying to eliminate, then why
not log all your queries to a SQL table and then do a select distinct from
the table, and get the substring of the query that does not include the
where clause?
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there a way to efficiently monitor SQL performance using SQL Profiler,
> and my meaning is to see distinct SQL query sent to server (by ignoring
> parameter values sent with the queries)
> this mean that query like
> select * from users where user_id = 10
> and
> select * from users where user_id = 20
> will be shown as the same query counted 2 times, and this way see which
> query is performed mostly on the server, and priorities it's performance
> tuning.
> creative suggestions invited.
> thanx.
>
>|||Thanx,
2 points:
1. how do i log all my queries to the database in an encapsulated way?
1.1 can i also log this way the time it took to execute?
2. my buttleneck might also be a execute statement - well, this will go also
with your suggestion, just truncated before the starting '('.
"Aaron Relph" <x@.x.com> wrote in message
news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> If it is simply the where clause that you are trying to eliminate, then
why
> not log all your queries to a SQL table and then do a select distinct from
> the table, and get the substring of the query that does not include the
> where clause?
> "z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
> news:Od5S7Gh%23DHA.3032@.TK2MSFTNGP10.phx.gbl...
Profiler,
>|||This may help -
http://www.sql-server-performance.c...ofiler_tips.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"z. f." <zigi@.info-scopeREMSPAM.co.il> wrote in message
news:OQuACAi%23DHA.3292@.TK2MSFTNGP11.phx.gbl...
> Thanx,
> 2 points:
> 1. how do i log all my queries to the database in an encapsulated way?
> 1.1 can i also log this way the time it took to execute?
> 2. my buttleneck might also be a execute statement - well, this will go
also
> with your suggestion, just truncated before the starting '('.
>
>
> "Aaron Relph" <x@.x.com> wrote in message
> news:OAEHgVh%23DHA.3436@.tk2msftngp13.phx.gbl...
> why
from
> Profiler,
ignoring
which
performance
>
Subscribe to:
Posts (Atom)