Hi All,
I am trying to perform a relatively simple SELECT query. Firstly heres my 3 tables im working on:
CREATE TABLE business_contact
(
BusContactID INT NOT NULL AUTO_INCREMENT,
Title VARCHAR(5),
Surname VARCHAR(30),
FirstName VARCHAR(30),
PRIMARY KEY (BusContactID)
) TYPE = INNODB;
CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
Manager VARCHAR(25),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;
CREATE TABLE works_for
(
CompanyID INT NOT NULL,
BusContactID INT NOT NULL,
Index (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES company (CompanyID) ON UPDATE CASCADE ON DELETE CASCADE,
Index (BusContactID),
FOREIGN KEY (BusContactID) REFERENCES business_contact (BusContactID) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (CompanyID, BusContactID)
) TYPE = INNODB;
The 'company' table quite obviously stores details about a range of companies, the 'business_contact' about business contacts and the 'works_for' table uses the PK values from the previous tables to associate a contact with a particular employer.
What i want to do is to retrieve a list of all the contacts and (if applicable) the name of the company they work for. My knowledge of SQL is relatively limited and so far ive managed to retrieve the all the contact and the where applicable, the key value of the company a contact works for. So all i really need to do is replace the key value with the company name, but, i dont know how!! :confused:
Heres my current query:
SELECT business_contact.*, works_for.buscontactid
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
Can anyone help me with fetching all the contacts in the table and if the contact works for a company then listing the name of the company?!?!?
Thanks in advance to anyone who can help
Damocles.SELECT business_contact.*, company.name
FROM business_contact
LEFT JOIN works_for
ON business_contact.buscontactid = works_for.buscontactid
LEFT JOIN company
ON works_for.companyid = company.companyid|||select business_contact.Title
, business_contact.Surname
, business_contact.FirstName
, company.Name
, company.Manager
from business_contact
left outer
join works_for
on business_contact.BusContactID
= works_for.BusContactID
left outer
join company
on works_for.CompanyID
= company.CompanyID|||Thanks to both for your replies, works great!
I could really do to read a decent SQL tutorial at some point!
Damocles.
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
Monday, March 26, 2012
Relational Database
Hi,
I have a very simple question.
In what cases are relational databases necessary?
Are they really necessary in cases where only a
single type of query is to be performed based on one unique
field or can we just put all fields together in a single database
and just access them through that unique field?Shwetabh (shwetabhgoel@.gmail.com) writes:
> I have a very simple question.
> In what cases are relational databases necessary?
> Are they really necessary in cases where only a
> single type of query is to be performed based on one unique
> field or can we just put all fields together in a single database
> and just access them through that unique field?
I have a very simple question.
In what cases are relational databases necessary?
Are they really necessary in cases where only a
single type of query is to be performed based on one unique
field or can we just put all fields together in a single database
and just access them through that unique field?Shwetabh (shwetabhgoel@.gmail.com) writes:
> I have a very simple question.
> In what cases are relational databases necessary?
> Are they really necessary in cases where only a
> single type of query is to be performed based on one unique
> field or can we just put all fields together in a single database
> and just access them through that unique field?
There are plenty of alternatievs to relational databases. There are object-
oriented databases, there are probably still some hierarchical databases
around, and there are systems that uses flat files.
But the relational databases dominate the market, probably because they
have proven to be very good at handling large amounts of data.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> Shwetabh (shwetabhgoel@.gmail.com) writes:
> > I have a very simple question.
> > In what cases are relational databases necessary?
> > Are they really necessary in cases where only a
> > single type of query is to be performed based on one unique
> > field or can we just put all fields together in a single database
> > and just access them through that unique field?
> There are plenty of alternatievs to relational databases. There are object-
> oriented databases, there are probably still some hierarchical databases
> around, and there are systems that uses flat files.
> But the relational databases dominate the market, probably because they
> have proven to be very good at handling large amounts of data.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Actually I am not asking about the alternatives. I just want to know
that are
relational databases really necessary for me if I require just a single
record
every time with no changes in structure, i.e I always need a record
based on
a unique value. Do i still need to create relations in the database or
am I better off
butting all fields in a single table and getting data from there.|||"Shwetabh" <shwetabhgoel@.gmail.com> wrote in message
news:1140086144.048226.309750@.g14g2000cwa.googlegr oups.com...
> Actually I am not asking about the alternatives. I just want to know
> that are
> relational databases really necessary for me if I require just a single
> record
> every time with no changes in structure, i.e I always need a record
> based on
> a unique value. Do i still need to create relations in the database or
> am I better off
> butting all fields in a single table and getting data from there.
More accurately, it sounds like you're asking whether you need to normalize
your database.
In this case probably not and using something like SQL Server may be
overkill. But without knowing more details, I don't think any of us can say
for sure.
|||Shwetabh wrote:
> Actually I am not asking about the alternatives. I just want to know
> that are
> relational databases really necessary for me if I require just a single
> record
> every time with no changes in structure, i.e I always need a record
> based on
> a unique value. Do i still need to create relations in the database or
> am I better off
> butting all fields in a single table and getting data from there.
I don't think you asked the right question. It seems you aren't asking
whether to use relational database systems but whether to normalize
your database or not.
The main motivation to normalize data is to preserve its integrity when
it is updated. A secondary reason is that normalization can help
performance by ensuring you aren't maintaining redundant data. Given
those factors you ought to have a good excuse if you don't normalize.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>> if I require just a single record every time with no changes in structure, i.e I always need a record based on a unique value. <<
There is nothing wrong with an indexed file, which is probably
supported by your host language. RDBMS is for large amounts of
inter-related data where integrity and portability are the big issue.|||It depends what you are doing.
If its a single row ever then just store the information in a xml document
on the file system.
If you are storing multiple rows then I'd consider using a database system
because you then dont have to roll your own data access code, having said
that, .NET has a number of facilities to help you there.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Shwetabh" <shwetabhgoel@.gmail.com> wrote in message
news:1140086144.048226.309750@.g14g2000cwa.googlegr oups.com...
> Erland Sommarskog wrote:
>> Shwetabh (shwetabhgoel@.gmail.com) writes:
>> > I have a very simple question.
>> > In what cases are relational databases necessary?
>> > Are they really necessary in cases where only a
>> > single type of query is to be performed based on one unique
>> > field or can we just put all fields together in a single database
>> > and just access them through that unique field?
>>
>> There are plenty of alternatievs to relational databases. There are
>> object-
>> oriented databases, there are probably still some hierarchical databases
>> around, and there are systems that uses flat files.
>>
>> But the relational databases dominate the market, probably because they
>> have proven to be very good at handling large amounts of data.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/pr...oads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodin...ions/books.mspx
> Actually I am not asking about the alternatives. I just want to know
> that are
> relational databases really necessary for me if I require just a single
> record
> every time with no changes in structure, i.e I always need a record
> based on
> a unique value. Do i still need to create relations in the database or
> am I better off
> butting all fields in a single table and getting data from there.sql
Relation between reads and duration
I have a query that performs strangely under different conditions as
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
follows:
When ran normally, it uses a clustered index scan. From the Profiler and
the IO statistics, this makes ~250,000 reads. This takes about 4 minutes to
run.
When ran with an index hint, it uses a clusted index seek and makes a
bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, this
takes only 1 minute to run.
Why is it like that? I have tried defragging the clustered index, but to no
avail. Running another query that also makes a clustered index scan makes
~250,000 reads, and takes ~ 4 minutes too.
What else can I check? Why does a query that makes less reads takes longer
to run than another that makes 4 times as many reads? I would like to avoid
having to force the hint, if I can help it.
Thx in advance.
Regards
Ray MondRay,
Do the 250K reads from the clustered index involve wide rows, so they
would access many data pages (maybe even 250,000)? With the hint, maybe
most of the million reads are from a relatively small number of pages in
the nonclustered index, and therefore from memory, not disk.
There are lots of factors involved, and sometimes there just isn't
enough information for the optimizer to choose the best plan. If you
provide some more information about your query (such as create table
statements, indexes, the actual queries and maybe even the good and bad
plans you're seeing), maybe we can give more specific help.
SK
Ray Mond wrote:
>I have a query that performs strangely under different conditions as
>follows:
>When ran normally, it uses a clustered index scan. From the Profiler and
>the IO statistics, this makes ~250,000 reads. This takes about 4 minutes t
o
>run.
>When ran with an index hint, it uses a clusted index seek and makes a
>bookmark lookup of about 180,000 rows. Reads are ~1,000,000. However, thi
s
>takes only 1 minute to run.
>Why is it like that? I have tried defragging the clustered index, but to n
o
>avail. Running another query that also makes a clustered index scan makes
>~250,000 reads, and takes ~ 4 minutes too.
>What else can I check? Why does a query that makes less reads takes longer
>to run than another that makes 4 times as many reads? I would like to avoi
d
>having to force the hint, if I can help it.
>Thx in advance.
>Regards
>Ray Mond
>
>|||Steve,
Average row size is 1,248 bytes. You are probably right, in that the
execution plan using the non-clustered index is reading data from pages
already in memory, because the data rows requested by the query are bunched
up together and not randomly distributed. Is there a way to know the actual
number of unique pages actually looked up by a query, short of peeking into
the contents of each page?
Thanks.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:OBrtou$BEHA.2620@.TK2MSFTNGP12.phx.gbl...
> Ray,
> Do the 250K reads from the clustered index involve wide rows, so they
> would access many data pages (maybe even 250,000)? With the hint, maybe
> most of the million reads are from a relatively small number of pages in
> the nonclustered index, and therefore from memory, not disk.
> There are lots of factors involved, and sometimes there just isn't
> enough information for the optimizer to choose the best plan. If you
> provide some more information about your query (such as create table
> statements, indexes, the actual queries and maybe even the good and bad
> plans you're seeing), maybe we can give more specific help.
> SK
> Ray Mond wrote:
>
to
this
no
makes
longer
avoid
>|||I don't know of a way, though maybe there's something in profiler that
I've never seen. I don't recall the details now, but I think the beta 1
release of SQL Server 2005 had some extra show statistics io column -
maybe the situation will improve in future versions of SQL Server.
SK
Ray Mond wrote:
>Steve,
>Average row size is 1,248 bytes. You are probably right, in that the
>execution plan using the non-clustered index is reading data from pages
>already in memory, because the data rows requested by the query are bunched
>up together and not randomly distributed. Is there a way to know the actua
l
>number of unique pages actually looked up by a query, short of peeking into
>the contents of each page?
>Thanks.
>
>|||Steve,
Thx. I'm using the SET STATISTICS IO output to get a ballpark figure of the
number of unique pages read.
Regards
Ray Mond
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RMkgJFCEHA.3064@.tk2msftngp13.phx.gbl...
> I don't know of a way, though maybe there's something in profiler that
> I've never seen. I don't recall the details now, but I think the beta 1
> release of SQL Server 2005 had some extra show statistics io column -
> maybe the situation will improve in future versions of SQL Server.
> SK
> Ray Mond wrote:
>
bunched
actual
into
>sql
relation & query
Hello,
I'm relative new to sql and databases and the last few weeks I learned
myself a lot. I'm trying to make a hotel reservation application.
I have a database with a table Booking, a table Room, a table
RoomsPerBooking. So a booking contains date/time etc and a field
RoomsPerBookingID. The table RoomsPerBooking contains number of
persons, unitprice etc. and a field ID and a field RoomID. The table
Room contains data like name, notes etc.
now i have two questions:
First about relations:
The table Booking has relationship: PK table RoomsPerBooking - ID <-->
FK table Booking - RoomsPerBookingID.
The table RoomsPerBookingID has relationship: PK table Room - ID <-->
FK table RoomsPerBooking - RoomID
Is this relationset good for my purpose? I think it is, but I am not
sure.
The second question is:
How do I get available rooms per night
I came this far... what are the "some statements"?
CREATE PROCEDURE dbo.GetAvailableRooms
(
@.BeginDate DATETIME,
@.EndDate DATETIME
)
AS
SELECT Room.*
FROM Room
WHERE Room.ID NOT IN (
SELECT DISTINCT room.ID
FROM Room room JOIN RoomsPerBooking roomsPerBooking
ON room.ID = roomsPerBooking.RoomID
--Some statements--
WHERE booking.FromDate <= @.EndDate
AND booking.ToDate >= @.BeginDate)
GOPlease 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.
Why do you have multiple names for the same data element? Why do you
use a singular name for a set of Rooms? Why did you use id and
room_id when the standard way of referencing a room is a "room number"?
Why do you use aliases that are the same as the base table names?
After you clean up the schema a bit, look at using a Calendar table.
Friday, March 23, 2012
Related Tables: Help Needed With JOIN Query
Hi Group,
My apologies for the lengthy post, but here goes...
I have the following tables:
TABLE Vehicles
(
[ID] nvarchar(5),
[Make] nvarchar(20),
[Model] nvarchar(20),
)
TABLE [Vehicle Status]
(
[ID] int, /* this is an auto-incrementing field*/
[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */
[Status] nvarchar(20),
[Status Date] datetime
)
Here's my problem...
I have the following data in my [Vehicles] and [Vehicle Status] tables:
[ID] [Make] [Model]
-------
H80 Nissan Skyline
H86 Toyota Aristo
[ID] [Vehicle ID] [Status] [Status Date]
------------
1 H80 OK 2006-10-01
2 H80 Damage 2006-10-05
3 H86 OK 2006-10-13
4 H86 Dent 2006-10-15
5 H86 Scratched 2006-10-16
I need a query that will join the two tables so that the most recent
status of each vehicle can be determined. I've gotten as far as:
SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM
[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [Vehicle
Status].[Vehicle ID]
Of course this produces the following results:
[ID] [Make] [Model] [Status] [Status Date]
--------------
H80 Nissan Skyline OK 2006-10-01
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo OK 2006-10-13
H86 Toyota Aristo Dent 2006-10-15
H86 Toyota Aristo Scratched 2006-10-16
How do I filter these results so that I get only the MOST RECENT vehicle
status?
i.e:
[ID] [Make] [Model] [Status] [Status Date]
--------------
H80 Nissan Skyline Damage 2006-10-05
H86 Toyota Aristo Scratched 2006-10-16
Thanks in advance,
Rommel the iCeMAn
*** Sent via Developersdex http://www.developersdex.com ***SELECT v.[ID], Make, Model, [Status], [Status Date]
FROM
[Vehicles] v INNER JOIN [Vehicle Status] vs ON v.[ID] = vs.[Vehicle ID]
and vs.[Status Date] = (select max(vs2.[Status Date]) from [Vehicle
Status] vs2 where vs2.[Vehicle ID] = vs.[Vehicle ID])
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***sql
related rooms query
hi, im working on this for a long time. i'm using MSsql-server2000
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.Hi
i have a table [visits] that records users visits to rooms. the columns are
room_id, user_id, visits.
i want to write a query that can calculate the top 10 rooms that are related
to any given room. i was thinking of firstly making a function that counts
how many users visited both room A and room B, and then running this
function on A and all other rooms, and order by the result. i keep getting
weird errors when doing that. please elaborate.Hi
Please post DDL (Create table statements you can use the generate SQL script
option in EM), example data (as insert statements), expected output and your
current queries. That will remove any ambiguity
It is not clear how you relate a users movement from one room to another,
what if the user has two browsers or shortcuts to specific rooms?
John
"Uri Lazar" <arielazar@.bezeqint.net> wrote in message
news:3f89b64e@.news.bezeqint.net...
> hi, im working on this for a long time. i'm using MSsql-server2000
> i have a table [visits] that records users visits to rooms. the columns
are
> room_id, user_id, visits.
> i want to write a query that can calculate the top 10 rooms that are
related
> to any given room. i was thinking of firstly making a function that counts
> how many users visited both room A and room B, and then running this
> function on A and all other rooms, and order by the result. i keep getting
> weird errors when doing that. please elaborate.|||Without DDL and example data I'm not sure I've fully understood your
requirement. Here's some assumed DDL and sample data:
CREATE TABLE RoomVisits (roomid INTEGER NOT NULL /* REFERENCES Rooms
(roomid) */, userid INTEGER NOT NULL /* REFERENCES Users (userid) */, visits
INTEGER NOT NULL CHECK (visits>0), PRIMARY KEY (roomid, userid))
INSERT INTO RoomVisits VALUES (1,100,1)
INSERT INTO RoomVisits VALUES (2,100,1)
INSERT INTO RoomVisits VALUES (3,100,4)
INSERT INTO RoomVisits VALUES (4,100,2)
INSERT INTO RoomVisits VALUES (1,222,2)
INSERT INTO RoomVisits VALUES (2,222,4)
Apparently for each room "A" you want the top 10 related rooms "B", ordered
by total number of visits to B. Rooms are deemed related if any user has
visited both - is that correct? If so, it seems a slightly artificial
requirement. Surely by that definition if users are making tours of rooms
then every room will inevitably become related to every other, unless there
are many more rooms than users.
Anyway, here's the query. First create a view which lists each related A-B
combination and the corresponding total number of visits to B.
CREATE VIEW Related_Room_Visits (room_A, room_B, visits_to_B)
AS
SELECT A.roomid, B.roomid, MAX(C.tot_visits)
FROM RoomVisits AS A
JOIN RoomVisits AS B
ON A.userid = B.userid AND A.roomid <> B.roomid
JOIN
(SELECT roomid, SUM(visits) AS tot_visits
FROM RoomVisits
GROUP BY roomid) AS C
ON B.roomid = C.roomid
GROUP BY A.roomid, B.roomid
Now display just the Top N for each room A. For my example data I've just
specified TOP 2 but you can change this as required:
SELECT R1.room_A, R1.room_B, R1.visits_to_B
FROM Related_Room_Visits AS R1
JOIN Related_Room_Visits AS R2
ON R1.room_A=R2.room_A AND R1.visits_to_B <= R2.visits_to_B
GROUP BY R1.room_A, R1.room_B, R1.visits_to_B
HAVING COUNT(*) <= 2 /* Top 2 for each Room_A */
ORDER BY R1.room_A, R1.room_B, R1.visits_to_b DESC
If this doesn't help then please post DDL, post some sample data as INSERT
statements and give an example of your required result.
--
David Portas
----
Please reply only to the newsgroup
--
Relace function of mysql
Hi,
Want to know that is their any option of inserting a record if the record does not exist in the table..while firing update query.
In mysql, we have replace which updates or inserts the record.
do let me know
thanx,
vivek.No SQL Server does not have a command like that. In my case all of my commands are done within a stored procedure, so I first check to see if the record exists, if yes then UPDATE else INSERT.
IF EXISTS (SELECT * FROM myTable WHERE pkey = @.pkey) BEGIN
UPDATE myTable
.
.
.
.
END ELSE
INSERT myTable
.
.
.
.
END|||Thanx,
will surely try with Stored procs.
regards,
vivek.
Want to know that is their any option of inserting a record if the record does not exist in the table..while firing update query.
In mysql, we have replace which updates or inserts the record.
do let me know
thanx,
vivek.No SQL Server does not have a command like that. In my case all of my commands are done within a stored procedure, so I first check to see if the record exists, if yes then UPDATE else INSERT.
IF EXISTS (SELECT * FROM myTable WHERE pkey = @.pkey) BEGIN
UPDATE myTable
.
.
.
.
END ELSE
INSERT myTable
.
.
.
.
END|||Thanx,
will surely try with Stored procs.
regards,
vivek.
Tuesday, March 20, 2012
Reinitilize subscribtions
I have trans replication and i want every sunday to make complete snapshot -
push replication. If I was able to make some command with query analyzer to
reinitilize subscribtions it would be great!
Thanks in advance!I believe you can change the snapshot agent schedule to do once on every
Sunday.
richard
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:uIdVvwK5DHA.488@.TK2MSFTNGP12.phx.gbl...
snapshot -
to
push replication. If I was able to make some command with query analyzer to
reinitilize subscribtions it would be great!
Thanks in advance!I believe you can change the snapshot agent schedule to do once on every
Sunday.
richard
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:uIdVvwK5DHA.488@.TK2MSFTNGP12.phx.gbl...
quote:
> I have trans replication and i want every sunday to make complete
snapshot -
quote:
> push replication. If I was able to make some command with query analyzer
to
quote:
> reinitilize subscribtions it would be great!
> Thanks in advance!
>
Reinitilize subscribtions
I have trans replication and i want every sunday to make complete snapshot -
push replication. If I was able to make some command with query analyzer to
reinitilize subscribtions it would be great!
Thanks in advance!I believe you can change the snapshot agent schedule to do once on every
Sunday.
richard
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:uIdVvwK5DHA.488@.TK2MSFTNGP12.phx.gbl...
> I have trans replication and i want every sunday to make complete
snapshot -
> push replication. If I was able to make some command with query analyzer
to
> reinitilize subscribtions it would be great!
> Thanks in advance!
>
push replication. If I was able to make some command with query analyzer to
reinitilize subscribtions it would be great!
Thanks in advance!I believe you can change the snapshot agent schedule to do once on every
Sunday.
richard
"Dalibor Cvijetinovic" <dalibor@.ice.si> wrote in message
news:uIdVvwK5DHA.488@.TK2MSFTNGP12.phx.gbl...
> I have trans replication and i want every sunday to make complete
snapshot -
> push replication. If I was able to make some command with query analyzer
to
> reinitilize subscribtions it would be great!
> Thanks in advance!
>
REINDEXING?
I have a big table with more than 7583117
records. The table is updated every day during the End of the day activity.
There is a job running a query on this table for updating another table.
The table is not reindexed and the job running freezes on the EXECUTING state many times is it because of not reindexing the indexes?
Thanks in advance
Jacx
Hi Jacx,
What dou you see in SQL Server Error Log?
In the moment of this job running you monitoring the locks?
Regards,
|||I checked the error log nothing about that particular job because it is not cancelled because of an error but it stays in the executing state.
|||When you say it "freezes" what do you mean, where are you seeing it "freeze"? Have you run the Profiler to see what is reallly going on?How are you updating the table and selecting the records? Are you doing:
INSERT INTO XXX
SELECT ......
If so, that is probably expanding tempdb when you see it "freeze" because it writtes the entire select to tempdb and then inserts it into the target table.
Without seeing exactly how you are "updating" the table, it is hard to say what it is doing. The index being corrupt and needing reindexing is not normally a problem in MS SQL.
Friday, March 9, 2012
regular primary keys vs autonumber primary keys
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.Originally posted by Chuckt
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.
If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.
(1) Joins are better utilized on integer fields and are faster.
(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.
(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.
HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but
t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)
This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.
It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.
Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)
Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."
Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:
/\
../\
.../\
...../\
....../\
instead of:
../\
./\/\
/\/\/\
blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.
Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.
...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.
blindman
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.
> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.
> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jj" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
Wednesday, March 7, 2012
Regular Disconnection occurs in query analyzer??
Hi,
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jéjé" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
today we have a strange case, my developpers are disconnected from the
server under query analyzer only from remote stations.
The user must refresh the connection at the higher level to be connected
again.
but every 10 to 15 minutes my users are disconnected again!!!
From what I can see, this event occurs for all clients at the same time.
Any idea?
I'm connected through Terminal server to my server to see if there is any
problem, and I don't see anything wrong.
There is nothing in the event log and nothing in the SQL Server log.
Thanks for your help.
Jerome.> From: "Jéjé" <willgart@._A_hAotmail_A_.com>
> Subject: Regular Disconnection occurs in query analyzer?
> Date: Tue, 27 Apr 2004 15:29:43 -0400
> Hi,
> today we have a strange case, my developpers are disconnected from the
> server under query analyzer only from remote stations.
> The user must refresh the connection at the higher level to be connected
> again.
> but every 10 to 15 minutes my users are disconnected again!!!
> From what I can see, this event occurs for all clients at the same time.
> Any idea?
> I'm connected through Terminal server to my server to see if there is any
> problem, and I don't see anything wrong.
> There is nothing in the event log and nothing in the SQL Server log.
> Thanks for your help.
> Jerome.
--
Hi Jerome,
Check the SQL Server errorlogs for any errors coinciding with the
disconnects. If nothing, you may have to capture a network trace to find
out why users are disconnecting.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional
Monday, February 20, 2012
Registered Server v/s Linked Server
Hello All,
What is the difference between a registered server and a linked server? If
in Query Analyzer one can reference objects using fully qualified names in a
linked server like this:
SELECT * FROM LinkedServer.Northwind.dbo.Products
why can't one do the same by registering the remote server (which is in the
same domain) and then referencing it?
Thanks somebody; help please. This has me baffled!
Jerome SmithRegistering servers if for the mmc console and information
that the snap in needs to access the server. A registered
server in the mmc console doesn't know anything about the
other registered servers, just the mmc console does.
With linked servers, the information is for SQL Server
itself so that it can access data or do whatever.
If registering servers automatically allowed the
functionality of linked servers, you'd lose a lot of control
over security, distributed queries, etc.
-Sue
On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
<jerosmith@.hotmail.com> wrote:
>Hello All,
>What is the difference between a registered server and a linked server? If
>in Query Analyzer one can reference objects using fully qualified names in a
>linked server like this:
>SELECT * FROM LinkedServer.Northwind.dbo.Products
>why can't one do the same by registering the remote server (which is in the
>same domain) and then referencing it?
>Thanks somebody; help please. This has me baffled!
>Jerome Smith
>|||Thanks Sue,
Now I still have the question: what is the purpose of registering servers?
What can one do with registered servers?
Thanks again.
Jerome
"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
> Registering servers if for the mmc console and information
> that the snap in needs to access the server. A registered
> server in the mmc console doesn't know anything about the
> other registered servers, just the mmc console does.
> With linked servers, the information is for SQL Server
> itself so that it can access data or do whatever.
> If registering servers automatically allowed the
> functionality of linked servers, you'd lose a lot of control
> over security, distributed queries, etc.
> -Sue
> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
> <jerosmith@.hotmail.com> wrote:
> >Hello All,
> >
> >What is the difference between a registered server and a linked server?
If
> >in Query Analyzer one can reference objects using fully qualified names
in a
> >linked server like this:
> >
> >SELECT * FROM LinkedServer.Northwind.dbo.Products
> >
> >why can't one do the same by registering the remote server (which is in
the
> >same domain) and then referencing it?
> >
> >Thanks somebody; help please. This has me baffled!
> >
> >Jerome Smith
> >
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03|||So that you can manage all the registered servers through
one mmc console using Enterprise Manager. A registered
server gives you access through Enterprise Manager.
-Sue
On Sat, 15 Nov 2003 00:19:11 -0400, "Jerome Smith"
<jsmith@.gauss.cl> wrote:
>Thanks Sue,
>Now I still have the question: what is the purpose of registering servers?
>What can one do with registered servers?
>Thanks again.
>Jerome
>"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
>news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
>> Registering servers if for the mmc console and information
>> that the snap in needs to access the server. A registered
>> server in the mmc console doesn't know anything about the
>> other registered servers, just the mmc console does.
>> With linked servers, the information is for SQL Server
>> itself so that it can access data or do whatever.
>> If registering servers automatically allowed the
>> functionality of linked servers, you'd lose a lot of control
>> over security, distributed queries, etc.
>> -Sue
>> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
>> <jerosmith@.hotmail.com> wrote:
>> >Hello All,
>> >
>> >What is the difference between a registered server and a linked server?
>If
>> >in Query Analyzer one can reference objects using fully qualified names
>in a
>> >linked server like this:
>> >
>> >SELECT * FROM LinkedServer.Northwind.dbo.Products
>> >
>> >why can't one do the same by registering the remote server (which is in
>the
>> >same domain) and then referencing it?
>> >
>> >Thanks somebody; help please. This has me baffled!
>> >
>> >Jerome Smith
>> >
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03
>
What is the difference between a registered server and a linked server? If
in Query Analyzer one can reference objects using fully qualified names in a
linked server like this:
SELECT * FROM LinkedServer.Northwind.dbo.Products
why can't one do the same by registering the remote server (which is in the
same domain) and then referencing it?
Thanks somebody; help please. This has me baffled!
Jerome SmithRegistering servers if for the mmc console and information
that the snap in needs to access the server. A registered
server in the mmc console doesn't know anything about the
other registered servers, just the mmc console does.
With linked servers, the information is for SQL Server
itself so that it can access data or do whatever.
If registering servers automatically allowed the
functionality of linked servers, you'd lose a lot of control
over security, distributed queries, etc.
-Sue
On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
<jerosmith@.hotmail.com> wrote:
>Hello All,
>What is the difference between a registered server and a linked server? If
>in Query Analyzer one can reference objects using fully qualified names in a
>linked server like this:
>SELECT * FROM LinkedServer.Northwind.dbo.Products
>why can't one do the same by registering the remote server (which is in the
>same domain) and then referencing it?
>Thanks somebody; help please. This has me baffled!
>Jerome Smith
>|||Thanks Sue,
Now I still have the question: what is the purpose of registering servers?
What can one do with registered servers?
Thanks again.
Jerome
"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
> Registering servers if for the mmc console and information
> that the snap in needs to access the server. A registered
> server in the mmc console doesn't know anything about the
> other registered servers, just the mmc console does.
> With linked servers, the information is for SQL Server
> itself so that it can access data or do whatever.
> If registering servers automatically allowed the
> functionality of linked servers, you'd lose a lot of control
> over security, distributed queries, etc.
> -Sue
> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
> <jerosmith@.hotmail.com> wrote:
> >Hello All,
> >
> >What is the difference between a registered server and a linked server?
If
> >in Query Analyzer one can reference objects using fully qualified names
in a
> >linked server like this:
> >
> >SELECT * FROM LinkedServer.Northwind.dbo.Products
> >
> >why can't one do the same by registering the remote server (which is in
the
> >same domain) and then referencing it?
> >
> >Thanks somebody; help please. This has me baffled!
> >
> >Jerome Smith
> >
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03|||So that you can manage all the registered servers through
one mmc console using Enterprise Manager. A registered
server gives you access through Enterprise Manager.
-Sue
On Sat, 15 Nov 2003 00:19:11 -0400, "Jerome Smith"
<jsmith@.gauss.cl> wrote:
>Thanks Sue,
>Now I still have the question: what is the purpose of registering servers?
>What can one do with registered servers?
>Thanks again.
>Jerome
>"Sue Hoegemeier" <Sue_H@.nomail.please> escribió en el mensaje
>news:u4p7rvko25h1l9e4objkancqto2q2395qr@.4ax.com...
>> Registering servers if for the mmc console and information
>> that the snap in needs to access the server. A registered
>> server in the mmc console doesn't know anything about the
>> other registered servers, just the mmc console does.
>> With linked servers, the information is for SQL Server
>> itself so that it can access data or do whatever.
>> If registering servers automatically allowed the
>> functionality of linked servers, you'd lose a lot of control
>> over security, distributed queries, etc.
>> -Sue
>> On Wed, 12 Nov 2003 22:51:58 -0400, "Jerome Smith"
>> <jerosmith@.hotmail.com> wrote:
>> >Hello All,
>> >
>> >What is the difference between a registered server and a linked server?
>If
>> >in Query Analyzer one can reference objects using fully qualified names
>in a
>> >linked server like this:
>> >
>> >SELECT * FROM LinkedServer.Northwind.dbo.Products
>> >
>> >why can't one do the same by registering the remote server (which is in
>the
>> >same domain) and then referencing it?
>> >
>> >Thanks somebody; help please. This has me baffled!
>> >
>> >Jerome Smith
>> >
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.536 / Virus Database: 331 - Release Date: 03-11-03
>
Subscribe to:
Posts (Atom)