|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Evaluation of conditionsI have a Join condition like
Select columns From Table1 T1 Inner JOIN Table2 T2 ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 In table1, I have two cols that store some string range values. Col1 can have inidividual entries and col1 and col2 may have range values. I want check if T2.Col1 is contained in the table1 or not. I want to make sure that this query is optimized. If first condition is matched, will the second condition be evaluated? Suggestions are welcome. Thanks in advance. -- Regards, Lalit Bhatia Lalit
If post your table's structure+ an expected result, so we will be able to not guessing but give you more accurate suggestion Have you looked at execution plan of the query? Do you really need such "complicated" query? Show quote "Lalit Bhatia" <x**@abc.com> wrote in message news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... >I have a Join condition like > Select columns > From Table1 T1 > Inner JOIN Table2 T2 > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 > > In table1, I have two cols that store some string range values. Col1 can > have inidividual entries and col1 and col2 may have range values. > I want check if T2.Col1 is contained in the table1 or not. > > I want to make sure that this query is optimized. > If first condition is matched, will the second condition be evaluated? > > Suggestions are welcome. > Thanks in advance. > -- > Regards, > Lalit Bhatia > > I have changed the name of tables and cols only. columns contain Phone
Numbers. Structure is same as I given in the query. I want search phone number in the range and individual numbers. I have a composite index on T1.Col1 and T1.Col2. and Table1 has huge data 1000000 records and table2 is also has good amount of data in production. In fact at development end we usually do not have that much amount of data. execution plan shows that it is using 49% from Table2 and 51% from Table1 using index because everything that I want is present in the Index itself. I would like to know, Is there any better way or this approach is ok or good. -- Show quoteRegards, Lalit Bhatia "Uri Dimant" <u***@iscar.co.il> wrote in message news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... > Lalit > > If post your table's structure+ an expected result, so we will be able to > not guessing but give you more accurate suggestion > > Have you looked at execution plan of the query? Do you really need such > "complicated" query? > > > > > > "Lalit Bhatia" <x**@abc.com> wrote in message > news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... > >I have a Join condition like > > Select columns > > From Table1 T1 > > Inner JOIN Table2 T2 > > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 > > > > In table1, I have two cols that store some string range values. Col1 can > > have inidividual entries and col1 and col2 may have range values. > > I want check if T2.Col1 is contained in the table1 or not. > > > > I want to make sure that this query is optimized. > > If first condition is matched, will the second condition be evaluated? > > > > Suggestions are welcome. > > Thanks in advance. > > -- > > Regards, > > Lalit Bhatia > > > > > > Lalit
Can you post DDL+ sample code? What is your query? CREATE TABLE #Test ( col VARCHAR(1000) ) INSERT INTO #Test VALUES ('04-9880124') INSERT INTO #Test VALUES ('03-1225055') INSERT INTO #Test VALUES ('06-5965555') INSERT INTO #Test VALUES ('02-9880149') INSERT INTO #Test VALUES ('03-9112124') INSERT INTO #Test VALUES ('09-8858828') Show quote "Lalit Bhatia" <x**@abc.com> wrote in message news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... >I have changed the name of tables and cols only. columns contain Phone > Numbers. > Structure is same as I given in the query. I want search phone number in > the > range and individual numbers. > I have a composite index on T1.Col1 and T1.Col2. > and Table1 has huge data 1000000 records and table2 is also has good > amount > of data in production. > In fact at development end we usually do not have that much amount of > data. > execution plan shows that it is using 49% from Table2 and 51% from Table1 > using index > because everything that I want is present in the Index itself. > > I would like to know, Is there any better way or this approach is ok or > good. > > -- > Regards, > Lalit Bhatia > > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... >> Lalit >> >> If post your table's structure+ an expected result, so we will be able to >> not guessing but give you more accurate suggestion >> >> Have you looked at execution plan of the query? Do you really need such >> "complicated" query? >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... >> >I have a Join condition like >> > Select columns >> > From Table1 T1 >> > Inner JOIN Table2 T2 >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 >> > >> > In table1, I have two cols that store some string range values. Col1 >> > can >> > have inidividual entries and col1 and col2 may have range values. >> > I want check if T2.Col1 is contained in the table1 or not. >> > >> > I want to make sure that this query is optimized. >> > If first condition is matched, will the second condition be evaluated? >> > >> > Suggestions are welcome. >> > Thanks in advance. >> > -- >> > Regards, >> > Lalit Bhatia >> > >> > >> >> > > Create Table PhoneList
( Code varchar(5), PhoneNumber varchar(25), PhoneNumberTo varchar(25) PRIMARY KEY Nonclustered (Code, PhoneNumber) ) Create Table PhoneReceip ( IDPhReceip INT, InPhoneNumber varchar(25) ) INSERT PhoneList values ('BS01', '6005231234', NULL) INSERT PhoneList values ('BS01', '6005231235', NULL) INSERT PhoneList values ('BS01', '6005241000', NULL) INSERT PhoneList values ('BS02', '6005241001', '6005241999') INSERT PhoneList values ('BS02', '6005251555', NULL) INSERT PhoneList values ('BS02', '6005255555', NULL) INSERT PhoneList values ('BS02', '6005264231', '6005264239') INSERT PhoneReceip values (10100, '6005231234') INSERT PhoneReceip values (10100, '6005231555') INSERT PhoneReceip values (10101, '6005241051') INSERT PhoneReceip values (10101, '6005251555') When I query for 10100 then '6005231234' should be returned and when I query 10101 both records should return because '6005241051' is in the range between '6005241001' and '6005241999' and '6005231555' is there as individual entry. Select InPhoneNumber From PhoneList T1 Inner JOIN PhoneReceip T2 ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or T2.InPhoneNumber Between T1.PhoneNumber and T1.PhoneNumberTo WHERE IDPhReceip = 10100 and Code = 'BS01' Phone Number is always 10 digit string. There is no DLL for this. just this query that is run inside the SP. -- Show quoteRegards, Lalit Bhatia "Uri Dimant" <u***@iscar.co.il> wrote in message news:e3WsI1FDGHA.1124@TK2MSFTNGP10.phx.gbl... > Lalit > > Can you post DDL+ sample code? > > What is your query? > > > > CREATE TABLE #Test > ( > col VARCHAR(1000) > ) > > INSERT INTO #Test VALUES ('04-9880124') > INSERT INTO #Test VALUES ('03-1225055') > INSERT INTO #Test VALUES ('06-5965555') > INSERT INTO #Test VALUES ('02-9880149') > INSERT INTO #Test VALUES ('03-9112124') > INSERT INTO #Test VALUES ('09-8858828') > > > > > > > "Lalit Bhatia" <x**@abc.com> wrote in message > news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... > >I have changed the name of tables and cols only. columns contain Phone > > Numbers. > > Structure is same as I given in the query. I want search phone number in > > the > > range and individual numbers. > > I have a composite index on T1.Col1 and T1.Col2. > > and Table1 has huge data 1000000 records and table2 is also has good > > amount > > of data in production. > > In fact at development end we usually do not have that much amount of > > data. > > execution plan shows that it is using 49% from Table2 and 51% from Table1 > > using index > > because everything that I want is present in the Index itself. > > > > I would like to know, Is there any better way or this approach is ok or > > good. > > > > -- > > Regards, > > Lalit Bhatia > > > > "Uri Dimant" <u***@iscar.co.il> wrote in message > > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... > >> Lalit > >> > >> If post your table's structure+ an expected result, so we will be able to > >> not guessing but give you more accurate suggestion > >> > >> Have you looked at execution plan of the query? Do you really need such > >> "complicated" query? > >> > >> > >> > >> > >> > >> "Lalit Bhatia" <x**@abc.com> wrote in message > >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... > >> >I have a Join condition like > >> > Select columns > >> > From Table1 T1 > >> > Inner JOIN Table2 T2 > >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 > >> > > >> > In table1, I have two cols that store some string range values. Col1 > >> > can > >> > have inidividual entries and col1 and col2 may have range values. > >> > I want check if T2.Col1 is contained in the table1 or not. > >> > > >> > I want to make sure that this query is optimized. > >> > If first condition is matched, will the second condition be evaluated? > >> > > >> > Suggestions are welcome. > >> > Thanks in advance. > >> > -- > >> > Regards, > >> > Lalit Bhatia > >> > > >> > > >> > >> > > > > > > select PhoneReceip.IDPhReceip
, PhoneReceip.InPhoneNumber from PhoneReceip join PhoneList on PhoneReceip.InPhoneNumber between PhoneList.PhoneNumber and COALESCE(PhoneList.PhoneNumberTo,PhoneList.PhoneNumber) *** Sent via Developersdex http://www.developersdex.com *** Lalit
Thanks for DDL SELECT * FROM PhoneReceip pp JOINPhoneList pl ON pp.InPhoneNumber BETWEEN pl.PhoneNumber AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber) WHERE IDPhReceip=10101 Show quote "Lalit Bhatia" <x**@abc.com> wrote in message news:eeyDGQGDGHA.272@TK2MSFTNGP10.phx.gbl... > Create Table PhoneList > ( > Code varchar(5), > PhoneNumber varchar(25), > PhoneNumberTo varchar(25) > PRIMARY KEY Nonclustered (Code, PhoneNumber) > ) > > Create Table PhoneReceip > ( > IDPhReceip INT, > InPhoneNumber varchar(25) > ) > > INSERT PhoneList values ('BS01', '6005231234', NULL) > INSERT PhoneList values ('BS01', '6005231235', NULL) > INSERT PhoneList values ('BS01', '6005241000', NULL) > INSERT PhoneList values ('BS02', '6005241001', '6005241999') > INSERT PhoneList values ('BS02', '6005251555', NULL) > INSERT PhoneList values ('BS02', '6005255555', NULL) > INSERT PhoneList values ('BS02', '6005264231', '6005264239') > > INSERT PhoneReceip values (10100, '6005231234') > INSERT PhoneReceip values (10100, '6005231555') > INSERT PhoneReceip values (10101, '6005241051') > INSERT PhoneReceip values (10101, '6005251555') > > When I query for 10100 then '6005231234' should be returned and when I > query > 10101 both records should return because '6005241051' is in the range > between '6005241001' and '6005241999' and '6005231555' is there as > individual entry. > > Select InPhoneNumber > From PhoneList T1 > Inner JOIN PhoneReceip T2 > ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or T2.InPhoneNumber > Between T1.PhoneNumber and T1.PhoneNumberTo > WHERE IDPhReceip = 10100 and Code = 'BS01' > > Phone Number is always 10 digit string. > > There is no DLL for this. just this query that is run inside the SP. > > -- > Regards, > Lalit Bhatia > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:e3WsI1FDGHA.1124@TK2MSFTNGP10.phx.gbl... >> Lalit >> >> Can you post DDL+ sample code? >> >> What is your query? >> >> >> >> CREATE TABLE #Test >> ( >> col VARCHAR(1000) >> ) >> >> INSERT INTO #Test VALUES ('04-9880124') >> INSERT INTO #Test VALUES ('03-1225055') >> INSERT INTO #Test VALUES ('06-5965555') >> INSERT INTO #Test VALUES ('02-9880149') >> INSERT INTO #Test VALUES ('03-9112124') >> INSERT INTO #Test VALUES ('09-8858828') >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... >> >I have changed the name of tables and cols only. columns contain Phone >> > Numbers. >> > Structure is same as I given in the query. I want search phone number >> > in >> > the >> > range and individual numbers. >> > I have a composite index on T1.Col1 and T1.Col2. >> > and Table1 has huge data 1000000 records and table2 is also has good >> > amount >> > of data in production. >> > In fact at development end we usually do not have that much amount of >> > data. >> > execution plan shows that it is using 49% from Table2 and 51% from > Table1 >> > using index >> > because everything that I want is present in the Index itself. >> > >> > I would like to know, Is there any better way or this approach is ok or >> > good. >> > >> > -- >> > Regards, >> > Lalit Bhatia >> > >> > "Uri Dimant" <u***@iscar.co.il> wrote in message >> > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... >> >> Lalit >> >> >> >> If post your table's structure+ an expected result, so we will be able > to >> >> not guessing but give you more accurate suggestion >> >> >> >> Have you looked at execution plan of the query? Do you really need >> >> such >> >> "complicated" query? >> >> >> >> >> >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... >> >> >I have a Join condition like >> >> > Select columns >> >> > From Table1 T1 >> >> > Inner JOIN Table2 T2 >> >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 >> >> > >> >> > In table1, I have two cols that store some string range values. Col1 >> >> > can >> >> > have inidividual entries and col1 and col2 may have range values. >> >> > I want check if T2.Col1 is contained in the table1 or not. >> >> > >> >> > I want to make sure that this query is optimized. >> >> > If first condition is matched, will the second condition be > evaluated? >> >> > >> >> > Suggestions are welcome. >> >> > Thanks in advance. >> >> > -- >> >> > Regards, >> >> > Lalit Bhatia >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > We can also use IsNULL instead of COALESCE here.
could you explain which one will be faster? -- Show quoteRegards, Lalit Bhatia "Uri Dimant" <u***@iscar.co.il> wrote in message news:%23NMmMwGDGHA.2292@tk2msftngp13.phx.gbl... > Lalit > > Thanks for DDL > > SELECT * > FROM PhoneReceip pp > JOINPhoneList pl > ON pp.InPhoneNumber > BETWEEN pl.PhoneNumber > AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber) > WHERE IDPhReceip=10101 > > > > "Lalit Bhatia" <x**@abc.com> wrote in message > news:eeyDGQGDGHA.272@TK2MSFTNGP10.phx.gbl... > > Create Table PhoneList > > ( > > Code varchar(5), > > PhoneNumber varchar(25), > > PhoneNumberTo varchar(25) > > PRIMARY KEY Nonclustered (Code, PhoneNumber) > > ) > > > > Create Table PhoneReceip > > ( > > IDPhReceip INT, > > InPhoneNumber varchar(25) > > ) > > > > INSERT PhoneList values ('BS01', '6005231234', NULL) > > INSERT PhoneList values ('BS01', '6005231235', NULL) > > INSERT PhoneList values ('BS01', '6005241000', NULL) > > INSERT PhoneList values ('BS02', '6005241001', '6005241999') > > INSERT PhoneList values ('BS02', '6005251555', NULL) > > INSERT PhoneList values ('BS02', '6005255555', NULL) > > INSERT PhoneList values ('BS02', '6005264231', '6005264239') > > > > INSERT PhoneReceip values (10100, '6005231234') > > INSERT PhoneReceip values (10100, '6005231555') > > INSERT PhoneReceip values (10101, '6005241051') > > INSERT PhoneReceip values (10101, '6005251555') > > > > When I query for 10100 then '6005231234' should be returned and when I > > query > > 10101 both records should return because '6005241051' is in the range > > between '6005241001' and '6005241999' and '6005231555' is there as > > individual entry. > > > > Select InPhoneNumber > > From PhoneList T1 > > Inner JOIN PhoneReceip T2 > > ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or T2.InPhoneNumber > > Between T1.PhoneNumber and T1.PhoneNumberTo > > WHERE IDPhReceip = 10100 and Code = 'BS01' > > > > Phone Number is always 10 digit string. > > > > There is no DLL for this. just this query that is run inside the SP. > > > > -- > > Regards, > > Lalit Bhatia > > "Uri Dimant" <u***@iscar.co.il> wrote in message > > news:e3WsI1FDGHA.1124@TK2MSFTNGP10.phx.gbl... > >> Lalit > >> > >> Can you post DDL+ sample code? > >> > >> What is your query? > >> > >> > >> > >> CREATE TABLE #Test > >> ( > >> col VARCHAR(1000) > >> ) > >> > >> INSERT INTO #Test VALUES ('04-9880124') > >> INSERT INTO #Test VALUES ('03-1225055') > >> INSERT INTO #Test VALUES ('06-5965555') > >> INSERT INTO #Test VALUES ('02-9880149') > >> INSERT INTO #Test VALUES ('03-9112124') > >> INSERT INTO #Test VALUES ('09-8858828') > >> > >> > >> > >> > >> > >> > >> "Lalit Bhatia" <x**@abc.com> wrote in message > >> news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... > >> >I have changed the name of tables and cols only. columns contain Phone > >> > Numbers. > >> > Structure is same as I given in the query. I want search phone number > >> > in > >> > the > >> > range and individual numbers. > >> > I have a composite index on T1.Col1 and T1.Col2. > >> > and Table1 has huge data 1000000 records and table2 is also has good > >> > amount > >> > of data in production. > >> > In fact at development end we usually do not have that much amount of > >> > data. > >> > execution plan shows that it is using 49% from Table2 and 51% from > > Table1 > >> > using index > >> > because everything that I want is present in the Index itself. > >> > > >> > I would like to know, Is there any better way or this approach is ok or > >> > good. > >> > > >> > -- > >> > Regards, > >> > Lalit Bhatia > >> > > >> > "Uri Dimant" <u***@iscar.co.il> wrote in message > >> > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... > >> >> Lalit > >> >> > >> >> If post your table's structure+ an expected result, so we will be able > > to > >> >> not guessing but give you more accurate suggestion > >> >> > >> >> Have you looked at execution plan of the query? Do you really need > >> >> such > >> >> "complicated" query? > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Lalit Bhatia" <x**@abc.com> wrote in message > >> >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... > >> >> >I have a Join condition like > >> >> > Select columns > >> >> > From Table1 T1 > >> >> > Inner JOIN Table2 T2 > >> >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and T1.Col2 > >> >> > > >> >> > In table1, I have two cols that store some string range values. Col1 > >> >> > can > >> >> > have inidividual entries and col1 and col2 may have range values. > >> >> > I want check if T2.Col1 is contained in the table1 or not. > >> >> > > >> >> > I want to make sure that this query is optimized. > >> >> > If first condition is matched, will the second condition be > > evaluated? > >> >> > > >> >> > Suggestions are welcome. > >> >> > Thanks in advance. > >> >> > -- > >> >> > Regards, > >> >> > Lalit Bhatia > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
Show quote "Lalit Bhatia" <x**@abc.com> wrote in message
news:O1KHIBHDGHA.2664@TK2MSFTNGP15.phx.gbl... > We can also use IsNULL instead of COALESCE here. > could you explain which one will be faster? > > -- > Regards, > Lalit Bhatia > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:%23NMmMwGDGHA.2292@tk2msftngp13.phx.gbl... >> Lalit >> >> Thanks for DDL >> >> SELECT * >> FROM PhoneReceip pp >> JOINPhoneList pl >> ON pp.InPhoneNumber >> BETWEEN pl.PhoneNumber >> AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber) >> WHERE IDPhReceip=10101 >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> news:eeyDGQGDGHA.272@TK2MSFTNGP10.phx.gbl... >> > Create Table PhoneList >> > ( >> > Code varchar(5), >> > PhoneNumber varchar(25), >> > PhoneNumberTo varchar(25) >> > PRIMARY KEY Nonclustered (Code, PhoneNumber) >> > ) >> > >> > Create Table PhoneReceip >> > ( >> > IDPhReceip INT, >> > InPhoneNumber varchar(25) >> > ) >> > >> > INSERT PhoneList values ('BS01', '6005231234', NULL) >> > INSERT PhoneList values ('BS01', '6005231235', NULL) >> > INSERT PhoneList values ('BS01', '6005241000', NULL) >> > INSERT PhoneList values ('BS02', '6005241001', '6005241999') >> > INSERT PhoneList values ('BS02', '6005251555', NULL) >> > INSERT PhoneList values ('BS02', '6005255555', NULL) >> > INSERT PhoneList values ('BS02', '6005264231', '6005264239') >> > >> > INSERT PhoneReceip values (10100, '6005231234') >> > INSERT PhoneReceip values (10100, '6005231555') >> > INSERT PhoneReceip values (10101, '6005241051') >> > INSERT PhoneReceip values (10101, '6005251555') >> > >> > When I query for 10100 then '6005231234' should be returned and when I >> > query >> > 10101 both records should return because '6005241051' is in the range >> > between '6005241001' and '6005241999' and '6005231555' is there as >> > individual entry. >> > >> > Select InPhoneNumber >> > From PhoneList T1 >> > Inner JOIN PhoneReceip T2 >> > ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or >> > T2.InPhoneNumber >> > Between T1.PhoneNumber and T1.PhoneNumberTo >> > WHERE IDPhReceip = 10100 and Code = 'BS01' >> > >> > Phone Number is always 10 digit string. >> > >> > There is no DLL for this. just this query that is run inside the SP. >> > >> > -- >> > Regards, >> > Lalit Bhatia >> > "Uri Dimant" <u***@iscar.co.il> wrote in message >> > news:e3WsI1FDGHA.1124@TK2MSFTNGP10.phx.gbl... >> >> Lalit >> >> >> >> Can you post DDL+ sample code? >> >> >> >> What is your query? >> >> >> >> >> >> >> >> CREATE TABLE #Test >> >> ( >> >> col VARCHAR(1000) >> >> ) >> >> >> >> INSERT INTO #Test VALUES ('04-9880124') >> >> INSERT INTO #Test VALUES ('03-1225055') >> >> INSERT INTO #Test VALUES ('06-5965555') >> >> INSERT INTO #Test VALUES ('02-9880149') >> >> INSERT INTO #Test VALUES ('03-9112124') >> >> INSERT INTO #Test VALUES ('09-8858828') >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> >> news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... >> >> >I have changed the name of tables and cols only. columns contain >> >> >Phone >> >> > Numbers. >> >> > Structure is same as I given in the query. I want search phone >> >> > number >> >> > in >> >> > the >> >> > range and individual numbers. >> >> > I have a composite index on T1.Col1 and T1.Col2. >> >> > and Table1 has huge data 1000000 records and table2 is also has >> >> > good >> >> > amount >> >> > of data in production. >> >> > In fact at development end we usually do not have that much amount >> >> > of >> >> > data. >> >> > execution plan shows that it is using 49% from Table2 and 51% from >> > Table1 >> >> > using index >> >> > because everything that I want is present in the Index itself. >> >> > >> >> > I would like to know, Is there any better way or this approach is ok > or >> >> > good. >> >> > >> >> > -- >> >> > Regards, >> >> > Lalit Bhatia >> >> > >> >> > "Uri Dimant" <u***@iscar.co.il> wrote in message >> >> > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... >> >> >> Lalit >> >> >> >> >> >> If post your table's structure+ an expected result, so we will be > able >> > to >> >> >> not guessing but give you more accurate suggestion >> >> >> >> >> >> Have you looked at execution plan of the query? Do you really need >> >> >> such >> >> >> "complicated" query? >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> >> >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... >> >> >> >I have a Join condition like >> >> >> > Select columns >> >> >> > From Table1 T1 >> >> >> > Inner JOIN Table2 T2 >> >> >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and >> >> >> > T1.Col2 >> >> >> > >> >> >> > In table1, I have two cols that store some string range values. > Col1 >> >> >> > can >> >> >> > have inidividual entries and col1 and col2 may have range values. >> >> >> > I want check if T2.Col1 is contained in the table1 or not. >> >> >> > >> >> >> > I want to make sure that this query is optimized. >> >> >> > If first condition is matched, will the second condition be >> > evaluated? >> >> >> > >> >> >> > Suggestions are welcome. >> >> >> > Thanks in advance. >> >> >> > -- >> >> >> > Regards, >> >> >> > Lalit Bhatia >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> > >> >> > >> >> >> >> >> > >> > >> >> > > Wow ,how could I forget this one
http://www.aspfaq.com/show.asp?id=2532 Show quote "Lalit Bhatia" <x**@abc.com> wrote in message news:O1KHIBHDGHA.2664@TK2MSFTNGP15.phx.gbl... > We can also use IsNULL instead of COALESCE here. > could you explain which one will be faster? > > -- > Regards, > Lalit Bhatia > "Uri Dimant" <u***@iscar.co.il> wrote in message > news:%23NMmMwGDGHA.2292@tk2msftngp13.phx.gbl... >> Lalit >> >> Thanks for DDL >> >> SELECT * >> FROM PhoneReceip pp >> JOINPhoneList pl >> ON pp.InPhoneNumber >> BETWEEN pl.PhoneNumber >> AND COALESCE(pl.PhoneNumberTo,pl.PhoneNumber) >> WHERE IDPhReceip=10101 >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> news:eeyDGQGDGHA.272@TK2MSFTNGP10.phx.gbl... >> > Create Table PhoneList >> > ( >> > Code varchar(5), >> > PhoneNumber varchar(25), >> > PhoneNumberTo varchar(25) >> > PRIMARY KEY Nonclustered (Code, PhoneNumber) >> > ) >> > >> > Create Table PhoneReceip >> > ( >> > IDPhReceip INT, >> > InPhoneNumber varchar(25) >> > ) >> > >> > INSERT PhoneList values ('BS01', '6005231234', NULL) >> > INSERT PhoneList values ('BS01', '6005231235', NULL) >> > INSERT PhoneList values ('BS01', '6005241000', NULL) >> > INSERT PhoneList values ('BS02', '6005241001', '6005241999') >> > INSERT PhoneList values ('BS02', '6005251555', NULL) >> > INSERT PhoneList values ('BS02', '6005255555', NULL) >> > INSERT PhoneList values ('BS02', '6005264231', '6005264239') >> > >> > INSERT PhoneReceip values (10100, '6005231234') >> > INSERT PhoneReceip values (10100, '6005231555') >> > INSERT PhoneReceip values (10101, '6005241051') >> > INSERT PhoneReceip values (10101, '6005251555') >> > >> > When I query for 10100 then '6005231234' should be returned and when I >> > query >> > 10101 both records should return because '6005241051' is in the range >> > between '6005241001' and '6005241999' and '6005231555' is there as >> > individual entry. >> > >> > Select InPhoneNumber >> > From PhoneList T1 >> > Inner JOIN PhoneReceip T2 >> > ON PhoneList.PhoneNumber = PhoneReceip.InPhoneNumber Or >> > T2.InPhoneNumber >> > Between T1.PhoneNumber and T1.PhoneNumberTo >> > WHERE IDPhReceip = 10100 and Code = 'BS01' >> > >> > Phone Number is always 10 digit string. >> > >> > There is no DLL for this. just this query that is run inside the SP. >> > >> > -- >> > Regards, >> > Lalit Bhatia >> > "Uri Dimant" <u***@iscar.co.il> wrote in message >> > news:e3WsI1FDGHA.1124@TK2MSFTNGP10.phx.gbl... >> >> Lalit >> >> >> >> Can you post DDL+ sample code? >> >> >> >> What is your query? >> >> >> >> >> >> >> >> CREATE TABLE #Test >> >> ( >> >> col VARCHAR(1000) >> >> ) >> >> >> >> INSERT INTO #Test VALUES ('04-9880124') >> >> INSERT INTO #Test VALUES ('03-1225055') >> >> INSERT INTO #Test VALUES ('06-5965555') >> >> INSERT INTO #Test VALUES ('02-9880149') >> >> INSERT INTO #Test VALUES ('03-9112124') >> >> INSERT INTO #Test VALUES ('09-8858828') >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> >> news:%23dOzhkFDGHA.2912@tk2msftngp13.phx.gbl... >> >> >I have changed the name of tables and cols only. columns contain >> >> >Phone >> >> > Numbers. >> >> > Structure is same as I given in the query. I want search phone >> >> > number >> >> > in >> >> > the >> >> > range and individual numbers. >> >> > I have a composite index on T1.Col1 and T1.Col2. >> >> > and Table1 has huge data 1000000 records and table2 is also has >> >> > good >> >> > amount >> >> > of data in production. >> >> > In fact at development end we usually do not have that much amount >> >> > of >> >> > data. >> >> > execution plan shows that it is using 49% from Table2 and 51% from >> > Table1 >> >> > using index >> >> > because everything that I want is present in the Index itself. >> >> > >> >> > I would like to know, Is there any better way or this approach is ok > or >> >> > good. >> >> > >> >> > -- >> >> > Regards, >> >> > Lalit Bhatia >> >> > >> >> > "Uri Dimant" <u***@iscar.co.il> wrote in message >> >> > news:eHxTJOFDGHA.3876@tk2msftngp13.phx.gbl... >> >> >> Lalit >> >> >> >> >> >> If post your table's structure+ an expected result, so we will be > able >> > to >> >> >> not guessing but give you more accurate suggestion >> >> >> >> >> >> Have you looked at execution plan of the query? Do you really need >> >> >> such >> >> >> "complicated" query? >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Lalit Bhatia" <x**@abc.com> wrote in message >> >> >> news:OrZpdLFDGHA.3140@TK2MSFTNGP14.phx.gbl... >> >> >> >I have a Join condition like >> >> >> > Select columns >> >> >> > From Table1 T1 >> >> >> > Inner JOIN Table2 T2 >> >> >> > ON T1.col1 = T2.col1 Or T2.Col1 Between T1.Col1 and >> >> >> > T1.Col2 >> >> >> > >> >> >> > In table1, I have two cols that store some string range values. > Col1 >> >> >> > can >> >> >> > have inidividual entries and col1 and col2 may have range values. >> >> >> > I want check if T2.Col1 is contained in the table1 or not. >> >> >> > >> >> >> > I want to make sure that this query is optimized. >> >> >> > If first condition is matched, will the second condition be >> > evaluated? >> >> >> > >> >> >> > Suggestions are welcome. >> >> >> > Thanks in advance. >> >> >> > -- >> >> >> > Regards, >> >> >> > Lalit Bhatia >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >  |
|||||||||||||||||||||||
Other interesting topics