|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Index QuestionI have a question on an index The query is as follows SELECT * FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To What index is more efficient CLUSTERED INDEX ON IP_Address_From and CLUSTERED INDEX ON IP_Address_To OR CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) Your help would be much appreciated Thanks Kathryn A table can only have 1 clustered index.
Because your doing a left join it may not efficiently use whatever index you create. as a rule of thumb You want to keep your clustered indexes smaller. Not knowing the datatype of the the ip_Address_From, and IP_Address_To columns its hard to say. It won't hurt to have a nonclustered index on ( IP_Address_From , IP_Address_To) But you need to evaluate your indexes on dbo.IP_Addr_Work also. Try using the index tuning wizard, and or reviewing your Execution plan. Show quote "Kathy" wrote: > Hi All again > > I have a question on an index > > The query is as follows > > SELECT * > FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > What index is more efficient > > CLUSTERED INDEX ON IP_Address_From > and > CLUSTERED INDEX ON IP_Address_To > > OR > > CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) > > Your help would be much appreciated > > Thanks > Kathryn > > > Kathy wrote:
Show quote > Hi All again Could you tell us what columns you really need back from that tables. I > > I have a question on an index > > The query is as follows > > SELECT * > FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > What index is more efficient > > CLUSTERED INDEX ON IP_Address_From > and > CLUSTERED INDEX ON IP_Address_To > > OR > > CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) > > Your help would be much appreciated > > Thanks > Kathryn can only assume you are not really wanting to pull in all rows from both tables. Also, is that the only join condition: "iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To" Or is there some other column or set of columns that join these tables. Could you also post the table DDL and explain what the query is supposed to do. Kathy,
Neither index is likely be really speed up the query. If you only need one (or two) columns from table IP_Address_City_Lkp (for each IP_Addr_Work.IPAddress) instead of all columns, then there might be a (much) more efficient way of writing the query. If that is what you needs, then please post back what you really need. Gert-Jan Kathy wrote: Show quote > > Hi All again > > I have a question on an index > > The query is as follows > > SELECT * > FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > What index is more efficient > > CLUSTERED INDEX ON IP_Address_From > and > CLUSTERED INDEX ON IP_Address_To > > OR > > CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) > > Your help would be much appreciated > > Thanks > Kathryn Hi Guys
Thanks for the input. If you can give me a more efficient way of doing this as well I would really appreciate it ok here is the scenario We have a lookup table with IP addresses and Countries These IP addresses are converted to an IP number and they exist in a range i.e. IP_TO 680000 IP_From 680100 We have a table with a list of our users IPs We convert the IP addresses in the users tables to a number Then we have to join the user table to the Lookup table to map the IP's to the countries because we need a list of all IP's with their countries The join I have is the only join I need to make but because it is a range one has to use between which may not be the best way to do this Keep in mind however that the list of user IP's is 11 million plus so the table is huge and we really need an effcient way to do this whether it is via an index or something else Here are the DDL's CREATE TABLE [IP_Addr_Work] ( [IPAddressId] [int] NOT NULL , [IPNumber] [bigint] NULL , [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IPStatus] [int] NULL ) ON [PRIMARY] CREATE TABLE [IP_Address_City_Lkp] ( [IP_Address_From] [bigint] NOT NULL , [IP_Address_To] [bigint] NOT NULL , [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] This is the join I need to do SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region, iacl.City FROM StagingDatabase.dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To Show quote "Kathy" <r**@roo.com> wrote in message news:VsudnXBMv5fwklnfRVn-rw@is.co.za... > Hi All again > > I have a question on an index > > The query is as follows > > SELECT * > FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > What index is more efficient > > CLUSTERED INDEX ON IP_Address_From > and > CLUSTERED INDEX ON IP_Address_To > > OR > > CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) > > Your help would be much appreciated > > Thanks > Kathryn > Kathy wrote:
Show quote > Hi Guys How do you convert the IP address to a number. Just curious. What > > Thanks for the input. > If you can give me a more efficient way of doing this as well I would > really appreciate it > > ok here is the scenario > > We have a lookup table with IP addresses and Countries > These IP addresses are converted to an IP number and they exist in a > range i.e. IP_TO 680000 IP_From 680100 > > We have a table with a list of our users IPs > We convert the IP addresses in the users tables to a number > > Then we have to join the user table to the Lookup table to map the > IP's to the countries because we need a list of all IP's with their > countries > The join I have is the only join I need to make but because it is a > range one has to use between which may not be the best way to do this > Keep in mind however that the list of user IP's is 11 million plus so > the table is huge and we really need an effcient way to do this > whether it is via an index or something else > > Here are the DDL's > > > CREATE TABLE [IP_Addr_Work] ( > [IPAddressId] [int] NOT NULL , > [IPNumber] [bigint] NULL , > [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [IPStatus] [int] NULL > ) ON [PRIMARY] > > CREATE TABLE [IP_Address_City_Lkp] ( > [IP_Address_From] [bigint] NOT NULL , > [IP_Address_To] [bigint] NOT NULL , > [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL , [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL ) ON [PRIMARY] > > This is the join I need to do > > SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus > , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region, > iacl.City FROM StagingDatabase.dbo.IP_Addr_Work AS iaw > LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > indexing options have you tested thus far and what did the execution plans show? Given your query, the big 11M row table is going to scan every time since you are returning all rows from that table. Try one composite clustered index on the IP Address From/To columns and see what comes back in the execution plan. I am going to work on some other execution plans
Do you have a more efficient way of doing that join perhaps besides and index ??? pehaps something else I could do besides a between Here you go - this is how the IP is converted -- Convert IP address in the form www.xxx.yyy.zzz into an IP -- number according to the formula: -- IP Number = 16777216*www + 65536*xxx + 256*yyy + zzz (1) CREATE FUNCTION dbo.udf_IP_Adress_to_IP_Number (@IP_Address CHAR(20)) RETURNS BIGINT AS BEGIN DECLARE @IP_Number BIGINT SELECT @IP_Number = CONVERT(BIGINT, SUBSTRING(@IP_Address, 1, CHARINDEX('.', @IP_Address) - 1)) * 16777216 SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', @IP_Address) + 1, 12) SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, SUBSTRING(@IP_Address, 1, CHARINDEX('.', @IP_Address) - 1)) * 65536 SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', @IP_Address) + 1, 12) SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, SUBSTRING(@IP_Address, 1, CHARINDEX('.', @IP_Address) - 1)) * 256 SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', @IP_Address) + 1, 12) SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, @IP_Address) RETURN @IP_Number END Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:%23sIqjigfFHA.2644@TK2MSFTNGP09.phx.gbl... > Kathy wrote: >> Hi Guys >> >> Thanks for the input. >> If you can give me a more efficient way of doing this as well I would >> really appreciate it >> >> ok here is the scenario >> >> We have a lookup table with IP addresses and Countries >> These IP addresses are converted to an IP number and they exist in a >> range i.e. IP_TO 680000 IP_From 680100 >> >> We have a table with a list of our users IPs >> We convert the IP addresses in the users tables to a number >> >> Then we have to join the user table to the Lookup table to map the >> IP's to the countries because we need a list of all IP's with their >> countries >> The join I have is the only join I need to make but because it is a >> range one has to use between which may not be the best way to do this >> Keep in mind however that the list of user IP's is 11 million plus so >> the table is huge and we really need an effcient way to do this >> whether it is via an index or something else >> >> Here are the DDL's >> >> >> CREATE TABLE [IP_Addr_Work] ( >> [IPAddressId] [int] NOT NULL , >> [IPNumber] [bigint] NULL , >> [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> [IPStatus] [int] NULL >> ) ON [PRIMARY] >> >> CREATE TABLE [IP_Address_City_Lkp] ( >> [IP_Address_From] [bigint] NOT NULL , >> [IP_Address_To] [bigint] NOT NULL , >> [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , >> [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS >> NULL , [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS >> NULL , [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS >> NULL ) ON [PRIMARY] >> >> This is the join I need to do >> >> SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus >> , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region, >> iacl.City FROM StagingDatabase.dbo.IP_Addr_Work AS iaw >> LEFT JOIN dbo.IP_Address_City_Lkp AS iacl >> ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To >> >> > How do you convert the IP address to a number. Just curious. What indexing > options have you tested thus far and what did the execution plans show? > Given your query, the big 11M row table is going to scan every time since > you are returning all rows from that table. Try one composite clustered > index on the IP Address From/To columns and see what comes back in the > execution plan. > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com Kathy wrote:
Show quote > I am going to work on some other execution plans I think you need to test the index and current design before making any > Do you have a more efficient way of doing that join perhaps besides > and index ??? > pehaps something else I could do besides a between > > Here you go - this is how the IP is converted > > -- Convert IP address in the form www.xxx.yyy.zzz into an IP > -- number according to the formula: > -- IP Number = 16777216*www + 65536*xxx + 256*yyy + zzz (1) > > CREATE FUNCTION dbo.udf_IP_Adress_to_IP_Number (@IP_Address CHAR(20)) > > RETURNS BIGINT > AS > BEGIN > DECLARE @IP_Number BIGINT > SELECT @IP_Number = CONVERT(BIGINT, SUBSTRING(@IP_Address, 1, > CHARINDEX('.', @IP_Address) - 1)) * 16777216 > SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', > @IP_Address) + 1, 12) > SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, > SUBSTRING(@IP_Address, 1, CHARINDEX('.', @IP_Address) - 1)) * 65536 > SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', > @IP_Address) + 1, 12) > SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, > SUBSTRING(@IP_Address, 1, CHARINDEX('.', @IP_Address) - 1)) * 256 > SELECT @IP_Address = SUBSTRING(@IP_Address, CHARINDEX('.', > @IP_Address) + 1, 12) > SELECT @IP_Number = @IP_Number + CONVERT(BIGINT, @IP_Address) > RETURN @IP_Number > END > decisions about another solution. Hi Kathy,
I suspected a scenario like this, that is why I asked extra info. Based on your narrative I will assume that the IP_From-IP_To are nonoverlapping ranges. In other words, I'm assuming that a particular will only have one (or zero) matches in the lookup table. The solution below depends on this assumption. The trick to use, is to rewrite the BETWEEN predicate (which forces SQL-Server to scan half the index for each row) to a lookup of the nearest value (which means only a few logical reads for each row) You could try the following query: SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus, iaw.IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region, iacl.City FROM ( SELECT IPAddressId, IPAddress, IPStatus, IPNumber,( SELECT MAX(IP_Address_From) FROM IP_Address_City_Lkp WHERE IP_Address_From < iaw2.IPNumber ) AS IP_From FROM StagingDatabase.dbo.IP_Addr_Work AS iaw2 ) AS iaw LEFT JOIN IP_Address_City_Lkp AS iacl ON iacl.IP_Address_From = iaw.IP_From AND iacl.IP_Address_To > iaw.IPNumber The inner query looksup the correct row from IP_Address_City_Lkp, or an incorrect row (one with an IP_Address_To < IPNumber). The left join in the outer query fetches the corresponding IP_Address_City_Lkp row or filters it out when it is out of range. By the way: you omitted the keys, constraints and indexes. The query above assumes that table IP_Address_City_Lkp has a clustered primary key on (IP_Address_From,IP_Address_To). Hope this helps, Gert-Jan Kathy wrote: Show quote > > Hi Guys > > Thanks for the input. > If you can give me a more efficient way of doing this as well I would really > appreciate it > > ok here is the scenario > > We have a lookup table with IP addresses and Countries > These IP addresses are converted to an IP number and they exist in a range > i.e. IP_TO 680000 IP_From 680100 > > We have a table with a list of our users IPs > We convert the IP addresses in the users tables to a number > > Then we have to join the user table to the Lookup table to map the IP's to > the countries because we need a list of all IP's with their countries > > The join I have is the only join I need to make but because it is a range > one has to use between which may not be the best way to do this > Keep in mind however that the list of user IP's is 11 million plus so the > table is huge and we really need an effcient way to do this whether it is > via an index or something else > > Here are the DDL's > > CREATE TABLE [IP_Addr_Work] ( > [IPAddressId] [int] NOT NULL , > [IPNumber] [bigint] NULL , > [IPAddress] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [IPStatus] [int] NULL > ) ON [PRIMARY] > > CREATE TABLE [IP_Address_City_Lkp] ( > [IP_Address_From] [bigint] NOT NULL , > [IP_Address_To] [bigint] NOT NULL , > [Country_Short] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [Country_Long] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [Region] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , > [City] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL > ) ON [PRIMARY] > > This is the join I need to do > > SELECT iaw.IPAddressId, iaw.IPAddress, iaw.IPStatus > , IPNumber , iacl.Country_Short, iacl.Country_Long, iacl.Region, iacl.City > FROM StagingDatabase.dbo.IP_Addr_Work AS iaw > LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > ON IPNumber BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > "Kathy" <r**@roo.com> wrote in message > news:VsudnXBMv5fwklnfRVn-rw@is.co.za... > > Hi All again > > > > I have a question on an index > > > > The query is as follows > > > > SELECT * > > FROM dbo.IP_Addr_Work AS iaw LEFT JOIN dbo.IP_Address_City_Lkp AS iacl > > ON iaw.IPAddress BETWEEN iacl.IP_Address_From AND iacl.IP_Address_To > > > > What index is more efficient > > > > CLUSTERED INDEX ON IP_Address_From > > and > > CLUSTERED INDEX ON IP_Address_To > > > > OR > > > > CLUSTERED INDEX ON ( IP_Address_From , IP_Address_To) > > > > Your help would be much appreciated > > > > Thanks > > Kathryn > >  |
|||||||||||||||||||||||
Other interesting topics