Correct SELECT Statement via joins

Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:

>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:

> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan
|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If you can have nulls then you'd have to change the INNER joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER returned even if you have a NULL last supplier or current supplier. Also the "LIKE '%something%'" is not a fantastic search because it will require a complete index scan, ie. it will have to search every single row in the index from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:

Correct SELECT Statement via joins

Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavSELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:

>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what
i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIER
S
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to b
e
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:

> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement
:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
> ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||Thanks for the reply... however you say the 'LIKE' is not efficient, can you
suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23
VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a
given ITEM row. If you can have nulls then you'd have to change the INNER
joins to OUTER joins (left outer joins) so that you still get the ITEMNUMBER
returned even if you have a NULL last supplier or current supplier. Also t
he "LIKE '%something%'" is not a fantastic search because it will require a
complete index scan, ie. it will have to search every single row in the inde
x from start to end, which is not terribly efficient.
HTH
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Gav wrote:
Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =
ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav|||There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
[vbcol=seagreen]
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =
> i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>

Correct SELECT Statement via joins

Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
GavThis is a multi-part message in MIME format.
--090105020707090300000908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER = i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or current
supplier. Also the "LIKE '%something%'" is not a fantastic search
because it will require a complete index scan, ie. it will have to
search every single row in the index from start to end, which is not
terribly efficient.
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
>Hi all,
>I cannot quite get my head around these outer/inner joins (if this is what i
>need).
>Basically I have 2 tables ie
>TABLE ITEMS
>PRIMARYKEY ITEMNUMBER
>FOREIGNKEY LASTSUPPLIER
>FOREIGNKEY CURRENTSUPPLIER
>TABLE SUPPLIERS
>PRIMARYKEY SUPPLIERNUMBER
>SUPPLIERNAME
>Currently I can get the information I need by using this sort of statement:
>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>ITEMS
>However this is not how I really want to do it... because I would like to be
>able to something like:
>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>Could anyone help me with this please?
>Thanks
>Gav
>
>
--090105020707090300000908
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</body>
</html>
--090105020707090300000908--|||Gav wrote on Fri, 3 Jun 2005 00:03:20 +0100:
> Hi all,
> I cannot quite get my head around these outer/inner joins (if this is what
> i need).
> Basically I have 2 tables ie
> TABLE ITEMS
> PRIMARYKEY ITEMNUMBER
> FOREIGNKEY LASTSUPPLIER
> FOREIGNKEY CURRENTSUPPLIER
> TABLE SUPPLIERS
> PRIMARYKEY SUPPLIERNUMBER
> SUPPLIERNAME
> Currently I can get the information I need by using this sort of statement:
> SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER => ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM
> SUPPLIERS WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS
> CURRENTSUPPLIERNAME FROM ITEMS
> However this is not how I really want to do it... because I would like to
> be able to something like:
> as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
> however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
> Could anyone help me with this please?
LASTSUPPLIERNAME is not the column name, it's the derived table alias. You'd
need to use LASTSUPPLIERNAME.SUPPLIERNAME to reference the column.
That said, you also still need to work on the joins, see the post from Mike
Hodgson.
Dan|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thanks for the reply... however you say the 'LIKE' is not efficient, can =you suggest a more efficient way to do this?
Thanks again
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_000C_01C56851.48FDF610
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks for the reply... however you say =the 'LIKE' is not efficient, can you suggest a more efficient way to do =this?
Thanks again
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav

---

--=_NextPart_000_000C_01C56851.48FDF610--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just tried it out and performance aside it's working great thanks
Gav
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message =news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
FROM ITEMS as i
INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER
INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =3D =i.CURRENTSUPPLIER
WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER =for a given ITEM row. If you can have nulls then you'd have to change =the INNER joins to OUTER joins (left outer joins) so that you still get =the ITEMNUMBER returned even if you have a NULL last supplier or current =supplier. Also the "LIKE '%something%'" is not a fantastic search =because it will require a complete index scan, ie. it will have to =search every single row in the index from start to end, which is not =terribly efficient.
HTH
--
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W =http://www.mallesons.com=20
Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
--=_NextPart_000_001D_01C56854.400BBDA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Just tried it out and performance aside =it's working great thanks
Gav
"Mike Hodgson" wrote in message news:%23VzsIQ9ZFHA.=2308@.TK2MSFTNGP14.phx.gbl...
SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAMEFROM ITEMS as =i INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =3D i.LASTSUPPLIER INNER JOIN SUPPLIERS =as c on c.SUPPLIERNUMBER =3D i.CURRENTSUPPLIERWHERE =l.SUPPLIERNAME LIKE '%MICROSOFT%'This is assuming you =can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER for a given ITEM row. If =you can have nulls then you'd have to change the INNER joins to OUTER joins =(left outer joins) so that you still get the ITEMNUMBER returned even if you =have a NULL last supplier or current supplier. Also the "LIKE ='%something%'" is not a fantastic search because it will require a complete index scan, =ie. it will have to search every single row in the index from start to end, =which is not terribly efficient.HTH
--mike =hodgson | database =administrator | mallesons =stephen jaquesT +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907E mailto:mike.hodgson@.mal=lesons.nospam.com | W http://www.mallesons.com Gav wrote: Hi all,
I cannot quite get my head around these outer/inner joins (if this is =what i need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of =statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM =SUPPLIERS WHERE SUPPLIERNUMBER =3D ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME =FROM ITEMS
However this is not how I really want to do it... because I would like =to be able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name =LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav

---

--=_NextPart_000_001D_01C56854.400BBDA0--|||This is a multi-part message in MIME format.
--040807050503000601030701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
There's nothing wrong with the LIKE operator per se. It's just when you
you compare it to a string that begins with '%' (such as '%MICROSOFT%'),
which basically means this data could start with anything and so could
be anywhere in the index so I'd better scan the entire index so we find
all occurrences. Just make the search more specific.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Gav wrote:
> Thanks for the reply... however you say the 'LIKE' is not efficient,
> can you suggest a more efficient way to do this?
> Thanks again
> Gav
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl...
> SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME
> FROM ITEMS as i
> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER = i.LASTSUPPLIER
> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER => i.CURRENTSUPPLIER
> WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'
>
> This is assuming you can't have a NULL LASTSUPPLIER or
> CURRENTSUPPLIER for a given ITEM row. If you can have nulls then
> you'd have to change the INNER joins to OUTER joins (left outer
> joins) so that you still get the ITEMNUMBER returned even if you
> have a NULL last supplier or current supplier. Also the "LIKE
> '%something%'" is not a fantastic search because it will require a
> complete index scan, ie. it will have to search every single row
> in the index from start to end, which is not terribly efficient.
> HTH
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Gav wrote:
>>Hi all,
>>I cannot quite get my head around these outer/inner joins (if this is what i
>>need).
>>Basically I have 2 tables ie
>>TABLE ITEMS
>>PRIMARYKEY ITEMNUMBER
>>FOREIGNKEY LASTSUPPLIER
>>FOREIGNKEY CURRENTSUPPLIER
>>TABLE SUPPLIERS
>>PRIMARYKEY SUPPLIERNUMBER
>>SUPPLIERNAME
>>Currently I can get the information I need by using this sort of statement:
>>SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =>>ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
>>WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
>>ITEMS
>>However this is not how I really want to do it... because I would like to be
>>able to something like:
>>as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
>>however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
>>Could anyone help me with this please?
>>Thanks
>>Gav
>>
>>
--040807050503000601030701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>There's nothing wrong with the LIKE operator per se. It's just
when you you compare it to a string that begins with '%' (such as
'%MICROSOFT%'), which basically means this data could start with
anything and so could be anywhere in the index so I'd better scan the
entire index so we find all occurrences. Just make the search more
specific.</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="miduaIEsjEaFHA.3384@.TK2MSFTNGP09.phx.gbl" type="cite">
<title></title>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<meta content="MSHTML 6.00.2900.2627" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Thanks for the reply... however you
say the 'LIKE' is not efficient, can you suggest a more efficient way
to do this?</font></div>
<div> </div>
<div><font face="Arial" size="2">Thanks again</font></div>
<div><font face="Arial" size="2">Gav</font></div>
<blockquote
style="border-left: 2px solid rgb(0, 0, 0); padding-right: 0px; padding-left: 5px; margin-left: 5px; margin-right: 0px;"
dir="ltr">
<div>"Mike Hodgson" <<a
href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mike.hodgson@.mallesons.nospam.com</a>>
wrote in message <a href="http://links.10026.com/?link=news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl">news:%23VzsIQ9ZFHA.2308@.TK2MSFTNGP14.phx.gbl</a>...</div>
<blockquote><tt>SELECT i.ITEMNUMBER, l.SUPPLIERNAME, c.SUPPLIERNAME</tt><br>
<tt>FROM ITEMS as i</tt><br>
<tt> INNER JOIN SUPPLIERS as l on l.SUPPLIERNUMBER =i.LASTSUPPLIER</tt><br>
<tt> INNER JOIN SUPPLIERS as c on c.SUPPLIERNUMBER =i.CURRENTSUPPLIER</tt><br>
<tt>WHERE l.SUPPLIERNAME LIKE '%MICROSOFT%'</tt><br>
</blockquote>
<tt><br>
This is assuming you can't have a NULL LASTSUPPLIER or CURRENTSUPPLIER
for a given ITEM row. If you can have nulls then you'd have to change
the INNER joins to OUTER joins (left outer joins) so that you still get
the ITEMNUMBER returned even if you have a NULL last supplier or
current supplier. Also the "LIKE '%something%'" is not a fantastic
search because it will require a complete index scan, ie. it will have
to search every single row in the index from start to end, which is not
terribly efficient.<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2">database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3668 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">F</font></b><font
face="Tahoma" size="2"> +61 (2) 9296 3885 |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">M</font></b><font
face="Tahoma" size="2"> +61 (408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma"
size="2"> <a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">mailto:mike.hodgson@.mallesons.nospam.com</a>
|</font><b><font face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=http://www.mallesons.com</a></font></span>">http://www.mallesons.com">http://www.mallesons.com</a></font></span>
</p>
</div>
<br>
<br>
Gav wrote:
<blockquote cite="midu0PpRd8ZFHA.2520@.TK2MSFTNGP09.phx.gbl"
type="cite">
<pre wrap="">Hi all,
I cannot quite get my head around these outer/inner joins (if this is what i
need).
Basically I have 2 tables ie
TABLE ITEMS
PRIMARYKEY ITEMNUMBER
FOREIGNKEY LASTSUPPLIER
FOREIGNKEY CURRENTSUPPLIER
TABLE SUPPLIERS
PRIMARYKEY SUPPLIERNUMBER
SUPPLIERNAME
Currently I can get the information I need by using this sort of statement:
SELECT *, (SELECT SUPPLIERNAME FROM SUPPLIERS WHERE SUPPLIERNUMBER =ITEMS.LASTSUPPLIER) AS LASTSUPPLIERNAME, (SELECT SUPPLIERNAME FROM SUPPLIERS
WHERE SUPPLIERNUMBER = ITEMS.CURRENTSUPPLIER) AS CURRENTSUPPLIERNAME FROM
ITEMS
However this is not how I really want to do it... because I would like to be
able to something like:
as above... WHERE LASTSUPPLIERNAME LIKE '%MICROSOFT%'
however this doesn't work... 'error Invalid column name LASTSUPPLIERNAME'
Could anyone help me with this please?
Thanks
Gav
</pre>
</blockquote>
</blockquote>
</blockquote>
</body>
</html>
--040807050503000601030701--

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.
A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegr oups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>
|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.
|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegro ups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegroups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegroups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>

Correct procedure for concurrent transactions

Hi,
I am struggeling to get my head around how to maximize throughput to
the server using several concurrent transactions. It seems I always
stumble into massive amount of deadlocks (in my test-environment)
which, the server dutyfully resolves, but performance suffers to below
concurrent values.
To illustrate my problem, I have one transaction which inserts around
50 interdepent rows in 10 tables.
Right now, the flow is something like ( pseudo language) :
BEGIN TRANSACTION
INSERT ROW IN A
SELECT ID FROM A
INSERT INTO B ID FROM TABLE A (along with other data)
INSERT INTO C ID FROM TABLE A (along with yet other data)
GET ID FROM TABLE C
INSERT INTO TABLE D ID FROM TABLE C
INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
INSERT ROW IN A
INSERT INTO E ID FROM TABLE A
END TRANSACTION
(etc etc)
The exact amount of work inside the transaction is variable, but
rulebased. I.e. depends on the nature of the input from the end-user.
This is all fine, when I run it single-threaded (ofcourse), but when i
run it multithreaded, deadlocks will occur with alarming rapidity. I
know it can be minimized by careful selection of indexes etc, but what
I'm really after, is a best practices way of doing it. Currently I've
just dumb-ed it down by insureing that only one thread runs the
transaction at a time, and performance is ... ok. Not great, but ok.
Someone suggested lumping all accesses so they weren't spread out, i.e.
rewriting it to
INSERT INTO A
GET ID FROM A as id1
INSERT INTO A
GET ID FROM A as id2
INSERT INTO A
GET ID FROM A as id3
INSERT INTO B id1
INSERT INTO B id2
INSERT INTO C id1
GET ID FROM C as id6
INSERT INTO C id1,id6
INSERT INTO C id3
etc
This is a fairly complex job to perform just to see if it works, so if
anybody has any input I'd appreciate it. My app has to work with
severel DMBS's so I would like to minimize the sqlserver specific
parts.
I'm not sure how much the above would give, as thread 2 would halt on
the first INSERT INTO A until thread1 called commit or rollback.
Synchronization within my app is (obviously) not going to do any good,
since the DBMS will lock the tables for me.
Currently I'm using MSSQL2000, which is in the projects
minimum-requirements.
My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
So: Any clues? Any links? Any books that would help me here?
The problem only gets worse, when I factor in read-only operations, and
other updates to some of the tables. Any word on how that is best
practiced?
Thx in advance.A couple things.
1. How are you getting the ID back?
2. Do you have proper indexes on each of the tables for these type
operations?
3. Why are you using Serializable mode? Nothing I see in your example
warrants that. Try Read Committed instead.
--
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106156798.520698.299220@.f14g2000cwb.googlegroups.com...
> Hi,
> I am struggeling to get my head around how to maximize throughput to
> the server using several concurrent transactions. It seems I always
> stumble into massive amount of deadlocks (in my test-environment)
> which, the server dutyfully resolves, but performance suffers to below
> concurrent values.
> To illustrate my problem, I have one transaction which inserts around
> 50 interdepent rows in 10 tables.
> Right now, the flow is something like ( pseudo language) :
> BEGIN TRANSACTION
> INSERT ROW IN A
> SELECT ID FROM A
> INSERT INTO B ID FROM TABLE A (along with other data)
> INSERT INTO C ID FROM TABLE A (along with yet other data)
> GET ID FROM TABLE C
> INSERT INTO TABLE D ID FROM TABLE C
> INSERT INTO C ID FORM TABLE A AND ID FROM TABLE C (in other column)
> INSERT ROW IN A
> INSERT INTO E ID FROM TABLE A
> END TRANSACTION
> (etc etc)
> The exact amount of work inside the transaction is variable, but
> rulebased. I.e. depends on the nature of the input from the end-user.
> This is all fine, when I run it single-threaded (ofcourse), but when i
> run it multithreaded, deadlocks will occur with alarming rapidity. I
> know it can be minimized by careful selection of indexes etc, but what
> I'm really after, is a best practices way of doing it. Currently I've
> just dumb-ed it down by insureing that only one thread runs the
> transaction at a time, and performance is ... ok. Not great, but ok.
> Someone suggested lumping all accesses so they weren't spread out, i.e.
> rewriting it to
> INSERT INTO A
> GET ID FROM A as id1
> INSERT INTO A
> GET ID FROM A as id2
> INSERT INTO A
> GET ID FROM A as id3
> INSERT INTO B id1
> INSERT INTO B id2
> INSERT INTO C id1
> GET ID FROM C as id6
> INSERT INTO C id1,id6
> INSERT INTO C id3
> etc
> This is a fairly complex job to perform just to see if it works, so if
> anybody has any input I'd appreciate it. My app has to work with
> severel DMBS's so I would like to minimize the sqlserver specific
> parts.
> I'm not sure how much the above would give, as thread 2 would halt on
> the first INSERT INTO A until thread1 called commit or rollback.
>
> Synchronization within my app is (obviously) not going to do any good,
> since the DBMS will lock the tables for me.
> Currently I'm using MSSQL2000, which is in the projects
> minimum-requirements.
> My tests have been using TRANSACTION_ISOLATION_SERIALIZABLE
> So: Any clues? Any links? Any books that would help me here?
> The problem only gets worse, when I factor in read-only operations, and
> other updates to some of the tables. Any word on how that is best
> practiced?
> Thx in advance.
>|||1) The table has an autoincrementing column, so I insert into it, and
read back the max value. For this, I need to be in serializable mode.
2) Yes; everything is indexed correctly, and constrained with foreign
keys.
3) Since read-committed would return a different max() for the ID I
think I need to be in serializable mode, right?
I know I could use some sql-server specific way of retrieving the IDs,
but that wouldnt alleiviate the locking occuring on the first ID
tables, that are held, until my transaction commits or rolls back.|||You will never get good performance in generating the id's that way. For
one you are essentially making the application single user with the over use
of the Serialization Isolation level. As such you force the app to do things
one at a time and all the others must wait until the ones before them are
done. Either use an IDENTITY() datatype or generate your own ID's with
something like this:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
That will allow you to generate ids for different tables at the same time
and there is no need for increasing the isolation level. YOu can generate
all the ID's for each of the tables before you Begin the transaction and
then simply do the inserts.
--
Andrew J. Kelly SQL MVP
<akj@.tmnet.dk> wrote in message
news:1106221353.508792.46620@.c13g2000cwb.googlegroups.com...
> 1) The table has an autoincrementing column, so I insert into it, and
> read back the max value. For this, I need to be in serializable mode.
> 2) Yes; everything is indexed correctly, and constrained with foreign
> keys.
> 3) Since read-committed would return a different max() for the ID I
> think I need to be in serializable mode, right?
> I know I could use some sql-server specific way of retrieving the IDs,
> but that wouldnt alleiviate the locking occuring on the first ID
> tables, that are held, until my transaction commits or rolls back.
>