Showing posts with label contains. Show all posts
Showing posts with label contains. Show all posts

Wednesday, March 21, 2012

Difficult Query question: TOP 1 + UNION + GROUP BY?!

Hi,
I need to perform an SQL Query, but I'm not able to find out how. It should
do the following stuff:
I have a table (tblSMS) that contains different SMS-records (each record
with a Number, a SendDate (DateTime-field) and a Campaign).
When I receive an SMS I need to find the following record(s):
- All the records with a SendDate between now (GETDATE() ) and the last 48
hours AND the Number-Field should be equal to my input
- But: In all the records that it selects, there can't be 2 with the same
Campaign
- At least the last record which has the same Number (ORDER BY SendDate = DateTime field) should be selected, even when the SendDate is smaller than
GETDATE - 48 hours...
This query works for the first and third objectives, but not for the
second...
SELECT * FROM
(SELECT TOP 1 tblSMS.* FROM tblSMS
WHERE (Number = '1234567890')
ORDER BY SendDate DESC) X
UNION
SELECT DISTINCT tblSMS.* FROM tblSMS
WHERE ((Number = '1234567890'))
AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
ORDER BY SendDate DESC
Does anybody knows how to do this?
Thanks a lot in advance,
PieterFirst, I don't understand what you mean exactly by your second statement "In
all the records that it selects, there can't be 2 with the same Campaign".
A little more explanation, with an example of a false result, should be
given.
Second, there is nothing in the WHERE clauses of your query about the
Campaign field.
Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
here) X" statement.
S. L.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to perform an SQL Query, but I'm not able to find out how. It
> should
> do the following stuff:
> I have a table (tblSMS) that contains different SMS-records (each record
> with a Number, a SendDate (DateTime-field) and a Campaign).
> When I receive an SMS I need to find the following record(s):
> - All the records with a SendDate between now (GETDATE() ) and the last 48
> hours AND the Number-Field should be equal to my input
> - But: In all the records that it selects, there can't be 2 with the same
> Campaign
> - At least the last record which has the same Number (ORDER BY SendDate => DateTime field) should be selected, even when the SendDate is smaller than
> GETDATE - 48 hours...
> This query works for the first and third objectives, but not for the
> second...
> SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
> UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
> Does anybody knows how to do this?
> Thanks a lot in advance,
> Pieter
>|||Well, my table tblSMS contains the fields "Campaign", "Number", "SendDate",
...
So when I don't want two select two records with the same Campaign I want to
say with that that there can't be 2 or more records selected thath has in
the field "Campaign" the same value.
Indeed there is nothing in the WHERE clause about the campaign-field, that's
the reason I wrote that the query works for the first and third objective,
but not for the second (the one with the Campaign-stuff).
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> First, I don't understand what you mean exactly by your second statement
"In
> all the records that it selects, there can't be 2 with the same Campaign".
> A little more explanation, with an example of a false result, should be
> given.
> Second, there is nothing in the WHERE clauses of your query about the
> Campaign field.
> Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
> here) X" statement.
> S. L.
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I need to perform an SQL Query, but I'm not able to find out how. It
> > should
> > do the following stuff:
> > I have a table (tblSMS) that contains different SMS-records (each record
> > with a Number, a SendDate (DateTime-field) and a Campaign).
> >
> > When I receive an SMS I need to find the following record(s):
> > - All the records with a SendDate between now (GETDATE() ) and the last
48
> > hours AND the Number-Field should be equal to my input
> > - But: In all the records that it selects, there can't be 2 with the
same
> > Campaign
> > - At least the last record which has the same Number (ORDER BY SendDate
=> > DateTime field) should be selected, even when the SendDate is smaller
than
> > GETDATE - 48 hours...
> >
> > This query works for the first and third objectives, but not for the
> > second...
> > SELECT * FROM
> > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > WHERE (Number = '1234567890')
> > ORDER BY SendDate DESC) X
> > UNION
> > SELECT DISTINCT tblSMS.* FROM tblSMS
> > WHERE ((Number = '1234567890'))
> > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > ORDER BY SendDate DESC
> >
> > Does anybody knows how to do this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||Unfortunately, you cannot define a query in terms of what it can NOT select.
You specify criteria that the rows must match. In this case, you need to
define what logic is used to select only 1 of many rows that match a
campaign. Since the last part of the union query you posted uses distinct,
I'm guessing that there is no real PK for the table; perhaps there is
nothing that can be relied on to uniquely identify a particular row. If so,
you may need to use a temp table along with some sort of identity column to
generate sufficient unique infomation for each row to accomplish your goal.
Once you have a way of uniquely identifying a row, you can use min, max, or
top to limit rows by campaign. Or use a cursor.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23qUydd25EHA.824@.TK2MSFTNGP11.phx.gbl...
> Well, my table tblSMS contains the fields "Campaign", "Number",
"SendDate",
> ...
> So when I don't want two select two records with the same Campaign I want
to
> say with that that there can't be 2 or more records selected thath has in
> the field "Campaign" the same value.
> Indeed there is nothing in the WHERE clause about the campaign-field,
that's
> the reason I wrote that the query works for the first and third objective,
> but not for the second (the one with the Campaign-stuff).
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> > First, I don't understand what you mean exactly by your second statement
> "In
> > all the records that it selects, there can't be 2 with the same
Campaign".
> > A little more explanation, with an example of a false result, should be
> > given.
> >
> > Second, there is nothing in the WHERE clauses of your query about the
> > Campaign field.
> >
> > Third, I'm not sure about the usefullness of the "SELECT * FROM
(subquery
> > here) X" statement.
> >
> > S. L.
> >
> > "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >
> > > I need to perform an SQL Query, but I'm not able to find out how. It
> > > should
> > > do the following stuff:
> > > I have a table (tblSMS) that contains different SMS-records (each
record
> > > with a Number, a SendDate (DateTime-field) and a Campaign).
> > >
> > > When I receive an SMS I need to find the following record(s):
> > > - All the records with a SendDate between now (GETDATE() ) and the
last
> 48
> > > hours AND the Number-Field should be equal to my input
> > > - But: In all the records that it selects, there can't be 2 with the
> same
> > > Campaign
> > > - At least the last record which has the same Number (ORDER BY
SendDate
> => > > DateTime field) should be selected, even when the SendDate is smaller
> than
> > > GETDATE - 48 hours...
> > >
> > > This query works for the first and third objectives, but not for the
> > > second...
> > > SELECT * FROM
> > > (SELECT TOP 1 tblSMS.* FROM tblSMS
> > > WHERE (Number = '1234567890')
> > > ORDER BY SendDate DESC) X
> > > UNION
> > > SELECT DISTINCT tblSMS.* FROM tblSMS
> > > WHERE ((Number = '1234567890'))
> > > AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> > > ORDER BY SendDate DESC
> > >
> > > Does anybody knows how to do this?
> > >
> > > Thanks a lot in advance,
> > >
> > > Pieter
> > >
> > >
> >
> >
>|||Pieter,
I think this may work for you. The WITH TIES is a trick that
uses the CASE expression in the ORDER BY clause to select
all records within the last two days as tied, but only a single
older record if there are no recent ones. Give it a try, even
if it looks wrong at first!
So this identifies the most recent 1234567890 row for
each Campaign value, regardless of date, then returns to you
only the top 1 with ties, which is either all results within the
last 2 days, or the one most recent result from earlier.
SELECT TOP 1 WITH TIES *
FROM tblSMS as t1
WHERE Number = '1234567890'
AND NOT EXISTS (
SELECT * FROM tblSMS as t2
WHERE t2.Number = '1234567890'
AND t2.Campaign = t1.Campaign
AND t2.SendDate > t1.SendDate
)
ORDER BY
CASE WHEN SendDate <= getdate() - 2
THEN SendDate
ELSE 0 END
GO
Steve Kass
Drew University
DraguVaso wrote:
>Hi,
>I need to perform an SQL Query, but I'm not able to find out how. It should
>do the following stuff:
>I have a table (tblSMS) that contains different SMS-records (each record
>with a Number, a SendDate (DateTime-field) and a Campaign).
>When I receive an SMS I need to find the following record(s):
>- All the records with a SendDate between now (GETDATE() ) and the last 48
>hours AND the Number-Field should be equal to my input
>- But: In all the records that it selects, there can't be 2 with the same
>Campaign
>- At least the last record which has the same Number (ORDER BY SendDate =>DateTime field) should be selected, even when the SendDate is smaller than
>GETDATE - 48 hours...
>This query works for the first and third objectives, but not for the
>second...
>SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
>UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
>Does anybody knows how to do this?
>Thanks a lot in advance,
>Pieter
>
>

Difficult Query question: TOP 1 + UNION + GROUP BY?!

Hi,
I need to perform an SQL Query, but I'm not able to find out how. It should
do the following stuff:
I have a table (tblSMS) that contains different SMS-records (each record
with a Number, a SendDate (DateTime-field) and a Campaign).
When I receive an SMS I need to find the following record(s):
- All the records with a SendDate between now (GETDATE() ) and the last 48
hours AND the Number-Field should be equal to my input
- But: In all the records that it selects, there can't be 2 with the same
Campaign
- At least the last record which has the same Number (ORDER BY SendDate =
DateTime field) should be selected, even when the SendDate is smaller than
GETDATE - 48 hours...
This query works for the first and third objectives, but not for the
second...
SELECT * FROM
(SELECT TOP 1 tblSMS.* FROM tblSMS
WHERE (Number = '1234567890')
ORDER BY SendDate DESC) X
UNION
SELECT DISTINCT tblSMS.* FROM tblSMS
WHERE ((Number = '1234567890'))
AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
ORDER BY SendDate DESC
Does anybody knows how to do this?
Thanks a lot in advance,
Pieter
First, I don't understand what you mean exactly by your second statement "In
all the records that it selects, there can't be 2 with the same Campaign".
A little more explanation, with an example of a false result, should be
given.
Second, there is nothing in the WHERE clauses of your query about the
Campaign field.
Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
here) X" statement.
S. L.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I need to perform an SQL Query, but I'm not able to find out how. It
> should
> do the following stuff:
> I have a table (tblSMS) that contains different SMS-records (each record
> with a Number, a SendDate (DateTime-field) and a Campaign).
> When I receive an SMS I need to find the following record(s):
> - All the records with a SendDate between now (GETDATE() ) and the last 48
> hours AND the Number-Field should be equal to my input
> - But: In all the records that it selects, there can't be 2 with the same
> Campaign
> - At least the last record which has the same Number (ORDER BY SendDate =
> DateTime field) should be selected, even when the SendDate is smaller than
> GETDATE - 48 hours...
> This query works for the first and third objectives, but not for the
> second...
> SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
> UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
> Does anybody knows how to do this?
> Thanks a lot in advance,
> Pieter
>
|||Well, my table tblSMS contains the fields "Campaign", "Number", "SendDate",
...
So when I don't want two select two records with the same Campaign I want to
say with that that there can't be 2 or more records selected thath has in
the field "Campaign" the same value.
Indeed there is nothing in the WHERE clause about the campaign-field, that's
the reason I wrote that the query works for the first and third objective,
but not for the second (the one with the Campaign-stuff).
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> First, I don't understand what you mean exactly by your second statement
"In[vbcol=seagreen]
> all the records that it selects, there can't be 2 with the same Campaign".
> A little more explanation, with an example of a false result, should be
> given.
> Second, there is nothing in the WHERE clauses of your query about the
> Campaign field.
> Third, I'm not sure about the usefullness of the "SELECT * FROM (subquery
> here) X" statement.
> S. L.
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Oq37ACr5EHA.828@.TK2MSFTNGP14.phx.gbl...
48[vbcol=seagreen]
same[vbcol=seagreen]
=[vbcol=seagreen]
than
>
|||Unfortunately, you cannot define a query in terms of what it can NOT select.
You specify criteria that the rows must match. In this case, you need to
define what logic is used to select only 1 of many rows that match a
campaign. Since the last part of the union query you posted uses distinct,
I'm guessing that there is no real PK for the table; perhaps there is
nothing that can be relied on to uniquely identify a particular row. If so,
you may need to use a temp table along with some sort of identity column to
generate sufficient unique infomation for each row to accomplish your goal.
Once you have a way of uniquely identifying a row, you can use min, max, or
top to limit rows by campaign. Or use a cursor.
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23qUydd25EHA.824@.TK2MSFTNGP11.phx.gbl...
> Well, my table tblSMS contains the fields "Campaign", "Number",
"SendDate",
> ...
> So when I don't want two select two records with the same Campaign I want
to
> say with that that there can't be 2 or more records selected thath has in
> the field "Campaign" the same value.
> Indeed there is nothing in the WHERE clause about the campaign-field,
that's[vbcol=seagreen]
> the reason I wrote that the query works for the first and third objective,
> but not for the second (the one with the Campaign-stuff).
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OAA8v3r5EHA.2012@.TK2MSFTNGP15.phx.gbl...
> "In
Campaign".[vbcol=seagreen]
(subquery[vbcol=seagreen]
record[vbcol=seagreen]
last[vbcol=seagreen]
> 48
> same
SendDate
> =
> than
>
|||Pieter,
I think this may work for you. The WITH TIES is a trick that
uses the CASE expression in the ORDER BY clause to select
all records within the last two days as tied, but only a single
older record if there are no recent ones. Give it a try, even
if it looks wrong at first!
So this identifies the most recent 1234567890 row for
each Campaign value, regardless of date, then returns to you
only the top 1 with ties, which is either all results within the
last 2 days, or the one most recent result from earlier.
SELECT TOP 1 WITH TIES *
FROM tblSMS as t1
WHERE Number = '1234567890'
AND NOT EXISTS (
SELECT * FROM tblSMS as t2
WHERE t2.Number = '1234567890'
AND t2.Campaign = t1.Campaign
AND t2.SendDate > t1.SendDate
)
ORDER BY
CASE WHEN SendDate <= getdate() - 2
THEN SendDate
ELSE 0 END
GO
Steve Kass
Drew University
DraguVaso wrote:

>Hi,
>I need to perform an SQL Query, but I'm not able to find out how. It should
>do the following stuff:
>I have a table (tblSMS) that contains different SMS-records (each record
>with a Number, a SendDate (DateTime-field) and a Campaign).
>When I receive an SMS I need to find the following record(s):
>- All the records with a SendDate between now (GETDATE() ) and the last 48
>hours AND the Number-Field should be equal to my input
>- But: In all the records that it selects, there can't be 2 with the same
>Campaign
>- At least the last record which has the same Number (ORDER BY SendDate =
>DateTime field) should be selected, even when the SendDate is smaller than
>GETDATE - 48 hours...
>This query works for the first and third objectives, but not for the
>second...
>SELECT * FROM
> (SELECT TOP 1 tblSMS.* FROM tblSMS
> WHERE (Number = '1234567890')
> ORDER BY SendDate DESC) X
>UNION
> SELECT DISTINCT tblSMS.* FROM tblSMS
> WHERE ((Number = '1234567890'))
> AND (SendDate > DATEADD(hh, -48 ,GETDATE()))
> ORDER BY SendDate DESC
>Does anybody knows how to do this?
>Thanks a lot in advance,
>Pieter
>
>

Sunday, March 11, 2012

Differential Backup not smaller than the full backup

Hi,

Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.

We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.

We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)

When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).

Is there anything we are missing, why are the differential backup that big?

Thanks for any advice.

Hi Math,

When u r setting Differential backup have u checked for options like
Append to the existing backup set
Overwrite all existing backup sets

when we use append to the existing backup set it appends to the existing differential backup set, so the differential backup obviously increases & even nearer to the full backup size.

Regards
Karna|||

Can you post the exact syntax that you're using?

That would help to narrow it down.

Thanks,

|||Hi,

After getting into SQL Server 2005 Management Studio, in the Backup task of certain database when u click on options u'll find option buttons such as
Append to the existing backup set
Overwrite all existing backup sets

From these above options u can choose any one of them, by default Append to the existing backup set is enabled.

I hope this would helpful.

Regards
Karna

Differential Backup not smaller than the full backup

Hi,

Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.

We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.

We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)

When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).

Is there anything we are missing, why are the differential backup that big?

Thanks for any advice.

Hi Math,

When u r setting Differential backup have u checked for options like
Append to the existing backup set
Overwrite all existing backup sets

when we use append to the existing backup set it appends to the existing differential backup set, so the differential backup obviously increases & even nearer to the full backup size.

Regards
Karna|||

Can you post the exact syntax that you're using?

That would help to narrow it down.

Thanks,

|||Hi,

After getting into SQL Server 2005 Management Studio, in the Backup task of certain database when u click on options u'll find option buttons such as
Append to the existing backup set
Overwrite all existing backup sets

From these above options u can choose any one of them, by default Append to the existing backup set is enabled.

I hope this would helpful.

Regards
Karna

Wednesday, March 7, 2012

Different Results with double quote

If i do the following SQL_queries:
select * from exampletable where CONTAINS(exampletable.exampletext, '
"Shure*" ')
select * from exampletable where CONTAINS(exampletable.exampletext, '
Shure* ')
The query with the double quotes returns much more result and most of them
do not contain the word shure. The query without the quotes returns the
correct rows. Whats the difference for the server?
I am using a Windows 2000 Server with SQL 2000 Server.
Thanks in andvance, Gerald.
Gerald wrote on Tue, 23 Jan 2007 14:50:01 -0800:

> If i do the following SQL_queries:
> select * from exampletable where CONTAINS(exampletable.exampletext, '
> "Shure*" ')
This is a prefix term search, which finds all words that start with the
letters Shure, for instance
Shure
Shurec
Shuretacal

> select * from exampletable where CONTAINS(exampletable.exampletext, '
> Shure* ')
The * is ignored as it only applies when double quotes are used, so only
rows with the word Shure are returned.
Dan
|||"Daniel Crichton" wrote:

> This is a prefix term search, which finds all words that start with the
> letters Shure, for instance
> Shure
> Shurec
> Shuretacal
Thx, Daniel. But in my case the query with the double quotes returns also
results, which do not contain any word starting with shure. It just returns
useless results.
thx, gerald
|||Gerald wrote on Wed, 24 Jan 2007 05:41:01 -0800:

> "Daniel Crichton" wrote:
>
> Thx, Daniel. But in my case the query with the double quotes returns also
> results, which do not contain any word starting with shure. It just
> returns useless results.
Strange. I just tested here using my own servers (both SQL 2000 and SQL
2005) with the following:
SELECT FullTitle FROM Product WHERE CONTAINS(Product.FullTitle,'"asp*"')
and
SELECT FullTitle FROM Product WHERE CONTAINS(Product.FullTitle,'asp*')
The results are as I expected - the first pulls back more titles (351
compared to 101), including ASP as a word, and other words that start wth
ASP such as ASP.NET. The second pulls back only those rows where the word
ASP occurs.
What language word breaker is your FTI using? Maybe there's some sort of
language subsitution going on. Or do you use the thesaurus?
Dan

Different results from like and contains

Hi guys,
I have 2 different queries that I expected to return the same results.
Where Name like '%fish%'
and
Where contains((Name),'("*fish*")')
The first returned 181 results and the 2nd returned 178 results.
On closer inspection I determined that the Contains was not returning Names
with words that had "fish" embedded as in:
bigfishtackle.com
flyfishing
kingfisher
I thought that the *fish* would return words where fish was a suffix, prefix
or both.
What am I doing wrong?
TIA
Olaf,
Thanks for the heads-up on that information.
"Olaf Pietsch" <olaf_pietsch@.online.ms> wrote in message
news:%23iWYMNLSIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hi John,
> "John Kotuby" <JohnKotuby@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:%23m1txyKSIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
> The search with "*..." (leading asterisk) is not possible with contains,
> only the "...*" is possible.
> BOL says only:
> <prefix_term>
> Specifies a match of words or phrases beginning with the specified text.
> Enclose a prefix term in double quotation marks ("") and add an asterisk
> (*) before the ending quotation mark, so that all text starting with the
> simple term specified before the asterisk is matched.
> http://msdn2.microsoft.com/en-us/library/ms187787.aspx
> Many Ragards,
> Olaf
> --
> Gru Olaf
> Ich untersttze PASS Deutschland e.V. (http://www.sqlpass.de)
> Blog (http://www.sqlpass.de/PASSUserBlogs/tabid/178/Default.aspx?BlogID=3)
> Regionalgruppe Kln/Bonn/Dsseldorf
> (http://www.sqlpass.de/Regionalgruppen/KoelnBonnDuesseldorf/tabid/81/Default.aspx)
>

Friday, February 24, 2012

Different font size and style in the same table cell

I have a table cell that contains two things:

well code and well type (two different data fields concatenated)

for example:

4WA53102 Verticall Well

I want the code to be 12 pt arial regular

I want the well type to be 8 point italic arial

4WA53102 Vertical Well

How do I do this? Thanks

Why not put well code and well type in adjacent table cells and then you can change the formatting on the cell well code is in and the formatting on the cell well type is in to be different from each other. I don't see how it would be different from what you want to do.

It isn't possible to do what you want the way you want to do it.

Different execution plan between stored proc and ad-hoc query

We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

cheers

James

If the system is heavily updated, the stats could get stale. Consider running [sp_updatestats 'resample'] or explicitly forcing an index (the latter is not really recommended unless you fully understand the consequences).

e.g.

select * from tb with (index(myindex))

|||

It's very quite possible to get two different plans. Take for example these two TSQL statements:

a)

declare @.x int

set @.x = 99

select * from t1 where col1 = @.x

b)

select * from t1 where col1=99

In a), the optimizer evalutes the whole batch, and cannot evalute the literal value of 99 since it's a parameter value set inside a batch. So it makes a guesstimate and optimizes for a given value which it thinks may be the most correct/optimal. Often times it is, often times it is not. In b), the statement is evaluated and immediately it knows the value of 99, so it can get a very accurate estimate, thus producing a more reliable plan.

The same holds true for stored procedures. If you create a stored procedure and pass in a parameter value used in your WHERE clause, the optimizer will most likely produce a far better estimate than if you created a procedure which declared variables, set them, and then used them in your WHERE clause. It's always beneficial in a proc to try not to declare variables, set them and then use them in a query. If you can pass them as a parameter, the query has a better chance of producing an optimal plan based on accurate estimates.

Where you can also get into trouble is if the plan is cached with a value that gave good estimates/performance at the time it was created, but then as time passes and changes occur to the tables, yes the stats can get out of date, and you'll need to either update stats or clear the cache to remove the stale stats/plan.

If you got lost with what i was trying to say above, forgive me, I'm sure it's documented in some whitepaper somewhere, but I know this is an issue as merge replication procs and triggers hit this kind of problem often.

|||

Thank you both for your comments. Greg, that makes sense and does explain why we may have been seeing the different plan.

This problem actually appeared again yesterday, although this time the ad-hoc query was using the same (flawed) plan as the proc. We have decided in this instance to use an index hint as there are really no circumstances where it should be doing a full scan. As OJ says, I know these are not really recommended but I think I'm happy for this to be one of the rare expections!

cheers

James

|||Which version of SQL Server are you using? If 2005 then you might opt for the "Optimize For" hint or "Option Recompile". When using an index hint, you run the risk of accidentally breaking code later if that index is dropped or changed to include different columns.