The folling query works but is it the most efficient way to write it from a
performance aspect?
The parameter can be set with the values (0,1 and %) these can be changed if
needed for the final query version.
The Detail.Automated database field is a datatype of bit
Should I be writing this to avoid the LIKE keyword?
-- only used for testing
DECLARE @.Auto AS char (1)
SET @.Auto = '%'
-- end test
SELECT DISTINCT Status.ID,
Info.ID,
Info.Text,
Detail.Automated,
Status.Status
FROM Status INNER JOIN Info
ON Status.ID = Info.ID
INNER JOIN Detail
ON Info.ID = Detail.ID
WHERE (Status.ID = 4)
AND (Detail.Automated LIKE @.AutoJim Abel (JimAbel@.discussions.microsoft.com) writes:
> The folling query works but is it the most efficient way to write it
> from a performance aspect? The parameter can be set with the values (0,1
> and %) these can be changed if needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
Using LIKE with bit looks quite strange. I would rather write:
AND (Detail.Automated = @.Auto OR @.Auto IS NULL)
And of course @.Auto would be declared as bit.
Whether the query you have is the best from the point of view of
performance is impossible to say, as this requires knowledge about
the tables, indexes, and the amount and distribution of the data in
the tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label aperformance. Show all posts
Showing posts with label aperformance. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Query output format question
Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:
> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
--
353
Date
--
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SBI have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:
> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>
Query output format question
Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:
> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>
Performance Solution' and I tried running a couple of the scripts that she
mentioned. In particular I ran this script:
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess
select datepart(ms,getdate()) "Milliseconds"
select getdate() "Date"'
The out put from this query looks like this for each database (headers are
mine):
DB Name Acess
-- --
DB1 1
Milliseconds
353
Date
2004-08-05 11:06:14.353
Ok - Here is my question, How can I format the output so that instead of
getting 3 lines for each database, I'll get only 1 line? Does my question
make sense?
Thanks for your help.
SB
I have not read the article yet, so I don't know what the intended purpose
of the Milliseconds and Date results are...
However, have you tried making the three select statements one statement?
exec sp_MSforeachdb 'SELECT
''?'' "DB Name", has_dbaccess(''?'') Acess,
datepart(ms,getdate()) AS Milliseconds,
getdate() AS Date'
Keith
"Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
> Hi All, I was reading Kalen's SQL Server Magazine article 'Anatomy of a
> Performance Solution' and I tried running a couple of the scripts that she
> mentioned. In particular I ran this script:
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess
> select datepart(ms,getdate()) "Milliseconds"
> select getdate() "Date"'
> The out put from this query looks like this for each database (headers are
> mine):
> DB Name Acess
> -- --
> DB1 1
> Milliseconds
> --
> 353
> Date
> --
> 2004-08-05 11:06:14.353
>
> Ok - Here is my question, How can I format the output so that instead of
> getting 3 lines for each database, I'll get only 1 line? Does my question
> make sense?
> Thanks for your help.
> SB
>
|||Oh My Keith,
I feel like a fool. I should have known that, it was so easy.
Thank you so very much,
Sara B
"Keith Kratochvil" wrote:
> I have not read the article yet, so I don't know what the intended purpose
> of the Milliseconds and Date results are...
> However, have you tried making the three select statements one statement?
> exec sp_MSforeachdb 'SELECT
> ''?'' "DB Name", has_dbaccess(''?'') Acess,
> datepart(ms,getdate()) AS Milliseconds,
> getdate() AS Date'
> --
> Keith
>
> "Sara Beth" <SaraBeth@.discussions.microsoft.com> wrote in message
> news:4877F3F9-E563-4E38-885D-6E1402470EB3@.microsoft.com...
>
Subscribe to:
Posts (Atom)