Query using schema to determine if Trigger exists

Hi,

I would like to know if there's a query that can tell me whether or not a trigger exists!

Thanks!

Quote:

Originally Posted by Barno77

Hi,

I would like to know if there's a query that can tell me whether or not a trigger exists!

Thanks!

Returns list of trigger names from a specific database

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTrigger') = 1

Regards

Jim :)|||Thank you! This helped me!

Query Tuning -- Where Exists?

Hello World!
The following query takes more than one hour to complete. Assuming the
tables are properly indexed, if this can be tuned for quicker return?
Besides, I wonder if WHERE EXISTS is always/usually more efficient than
INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THANK
S.
SELECT DISTINCT count(*)
FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
ON
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))=d
d.DATE_FULL
AND dm.MINUTE_NUMBER_24=t.[Time]
AND dt.HQ_ID=t.HQ_ID
AND dt.Store_ID=t.Store_ID
AND dt.Dept_ID=t.Dept_ID
AND Convert(bigint,fs.Invoice_No)=(
CASE WHEN t.TransactionType='CreditCard' THEN
Convert(bigint,LEFT(t.JoinKey,4) )
ELSE Convert(bigint,t.JoinKey)
END
)
WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1Hi
Have a look at execution plan of the query. Does it use any indexes?
I have my doubt ,because in WHERE condition a LEFT function would not
probably allow to optimyzer to use the index.
How is selective the columns that participate in WHERE condition?
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com...
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many
THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Hi
Have you tried to look at the execution plan? You should get a good idea
where/how its spending its time. Optimizing without the plan is rather
difficult.
You can use the SQL Query Analyzer that comes with the Enterprise
Manager's client-side tool set.
-David
C TO wrote:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THA
NKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
> Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))
=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Your query relies on doing calculations to join between tables and to filter
results. Because of this, SQL Server cannot use indexes to do quick lookups
and will instead do a full table scan, running the calculation on each row
to try to get a match. Try taking the calculations from the query and
perhaps using them to produce indexable calculated column(s).
In news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com,
C TO <CTO@.discussions.microsoft.com> said:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient
> than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how?
> Many THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) =
> 1
Stevesql

Query to know if a field exits in a table

In MsSQLServer what is the query to kown if a field exists in a table
if exist (select * from dbo.sysobjects where id =............
??SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'MyTable' = TABLE_NAME-PatP|||select 1
from syscolumns
where ID = Object_ID ( '<table_name>' ) and
Name = '<field_name'

bEH

Query to get column name with specific value

Here is tested schema

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO

CREATE TABLE [dbo].[TestTable] (
[SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into testtable values ('123','pass',null,'fail','skip')
insert into testtable values ('456','fail',null,'pass','skip')
insert into testtable values ('789',null,'fail','skip','pass')
insert into testtable values ('345','pass','pass','pass','fail')

I would like to fetch the COLUMNNAME where the value is fail.
Basically I need to know which test failed, test1, test2, test3 or
test4?

Is this possible?One method:

SELECT CASE
WHEN test1 = 'fail' THEN 'test1'
WHEN test2 = 'fail' THEN 'test2'
WHEN test3 = 'fail' THEN 'test3'
WHEN test4 = 'fail' THEN 'test4'
END AS Test
FROM testtable

This could also be accomplished with a rather ugly dynamic SQL script but
I'd rather not go there. You might consider revising your schema to
eliminate the repeating data. It's a lot easier to query data when your
data is in 1NF. Suggested alternative:

CREATE TABLE TestTable
(
SerialNumber char (12) NOT NULL,
TestNumber int NOT NULL,
TestResult varchar(10),
CONSTRAINT PK_TestTable PRIMARY KEY(SerialNumber, TestNumber)
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"kj" <kjaggi@.hotmail.com> wrote in message
news:665416be.0409071642.3710dac0@.posting.google.c om...
> Here is tested schema
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[TestTable]
> GO
> CREATE TABLE [dbo].[TestTable] (
> [SerialNumber] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [test1] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test2] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test3] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [test4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into testtable values ('123','pass',null,'fail','skip')
> insert into testtable values ('456','fail',null,'pass','skip')
> insert into testtable values ('789',null,'fail','skip','pass')
> insert into testtable values ('345','pass','pass','pass','fail')
>
> I would like to fetch the COLUMNNAME where the value is fail.
> Basically I need to know which test failed, test1, test2, test3 or
> test4?
> Is this possible?|||Thanks. I have already changed the schema. This is legacy stuff I am
trying to clean up but I needed to migrate the data to the new schema.

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

Query to determine if something exists versus just trying to create it...

I have run into two situations in the recent past that both have the
same thing in common. I have to preface this with the fact that I am
running the following queries in a C#/.Net environment using SQL Server
Express...
The question is, and it may be silly, but, should one query for the
existance of a row in the DB before attempting to create it?
For example, I could write a query that says "select * from table where
id = 1". Then if the resulting dataset has one or 0 rows, I could
determine if I need to write a Update or Insert query to put in the
row. However, I am assuming that the rows will 99% of the time already
exist, but I need to update all of the fields in the table with
potentially new values. So the question is, in this case, it would be
silly to attempt to determine if the row is there, then to determine if
it should be updated or not. Basically, I am executing the update
statement first, and if it returns 0 (meaning that the row didn't
exist), then I translate the statement from an update to an insert...
I am also doing something similar with create/alter statements, where I
could test if the table/field exists in the DB before attempting to
create or alter the table, but why bother if I can execute the one
statement and then know if the table or field already existed based off
of if the statement executed correctly or not... ?
So, is there a faster way to say "insert OR update this data", in one
statement, or should I just continue executing one, then the other if
the first one fails?
AB> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values.
If the usual case is that the row will exist, you might consider trying the
update first and then proceeding with the insert only if no rows were
updated. Something like:
CREATE PROC usp_SaveMyTable
@.MyTableId int,
@.SomeColumn int
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE @.Error int, @.RowCount int
BEGIN TRAN
UPDATE dbo.MyTable
SET SomeColumn = @.SomeColumn
WHERE MyTableId = @.MyTableId
SELECT @.Error = @.@.ERROR, @.RowCount = @.@.ROWCOUNT
IF @.RowCount > 0 OR @.Error <> 0
BEGIN
GOTO Done
END
INSERT INTO dbo.MyTable(MyTableId, SomeColumn)
SELECT
@.MyTableId, @.SomeColumn
WHERE NOT EXISTS
(
SELECT *
FROM dbo.MyTable WITH (HOLDLOCK)
WHERE MyTableId = @.MyTableId
)
SELECT @.Error = @.@.ERROR
Done:
IF @.Error = 0
BEGIN
COMMIT
END
ELSE
BEGIN
ROLLBACK
END
GO

> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
Personally, I prefer to avoid raising errors for expected conditions. If
you get error messages even when the script runs successfully, it's hard to
tell a real problem with all the noise.
IF OBJECT_ID('dbo.MyTable') IS NULL
BEGIN
CREATE TABLE dbo.MyTable
(
MyTableId int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
SomeColumn int NOT NULL
)
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Beavis" <multiformity@.gmail.com> wrote in message
news:1156385524.486272.138630@.p79g2000cwp.googlegroups.com...
>I have run into two situations in the recent past that both have the
> same thing in common. I have to preface this with the fact that I am
> running the following queries in a C#/.Net environment using SQL Server
> Express...
> The question is, and it may be silly, but, should one query for the
> existance of a row in the DB before attempting to create it?
> For example, I could write a query that says "select * from table where
> id = 1". Then if the resulting dataset has one or 0 rows, I could
> determine if I need to write a Update or Insert query to put in the
> row. However, I am assuming that the rows will 99% of the time already
> exist, but I need to update all of the fields in the table with
> potentially new values. So the question is, in this case, it would be
> silly to attempt to determine if the row is there, then to determine if
> it should be updated or not. Basically, I am executing the update
> statement first, and if it returns 0 (meaning that the row didn't
> exist), then I translate the statement from an update to an insert...
> I am also doing something similar with create/alter statements, where I
> could test if the table/field exists in the DB before attempting to
> create or alter the table, but why bother if I can execute the one
> statement and then know if the table or field already existed based off
> of if the statement executed correctly or not... ?
> So, is there a faster way to say "insert OR update this data", in one
> statement, or should I just continue executing one, then the other if
> the first one fails?
> AB
>

Query takes 3 mins to execute

Hi all, Iam new to DBA. I have tables with created indexes on all most all of
the columns. millions of records exists in this table. when i run the below
query, it is taking nearly 3 minutes to execute. How can i minimise the query
time ?
SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
POSTCODE, DATE_OF_BIRTH
From TBL_Patient
where Merged_Into_ID is NULL AND inactive='N' and
PATIENT_ID NOT IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
HAVING (COUNT(*) >=2)
Also i would like to know how a query time can be minimized (on what factors
the query time depends?)On Wed, 2 Aug 2006 06:02:02 -0700, Vikas
<Vikas@.discussions.microsoft.com> wrote:
>Hi all, Iam new to DBA. I have tables with created indexes on all most all of
>the columns. millions of records exists in this table. when i run the below
>query, it is taking nearly 3 minutes to execute. How can i minimise the query
>time ?
Creating indexes on most of the columns is not usually the answer.
Creating them on the right columns, and on the right sets of columns
(with the columns in the right order) requires understanding of what
queries will be doing.
>SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
>POSTCODE, DATE_OF_BIRTH
> From TBL_Patient
> where Merged_Into_ID is NULL AND inactive='N' and
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> HAVING (COUNT(*) >=2)
>Also i would like to know how a query time can be minimized (on what factors
>the query time depends?)
Using TOP without ORDER BY makes little sense. I would either drop
the TOP 100, or add "ORDER BY cntDuplicatePat DESC".
I would not expect indexing of TBL_Patient to help much with this
query. The only way it might is if a very small percentage of
patients were active, or a very small percentage satisfied the "
Merged_Into_ID is NULL" test.
However, indexing of TBL_PATIENT_DUPLICATES is another matter. How
many rows are in TBL_PATIENT_DUPLICATES? How many columns? How many
satisfy the test STATUS <> 2? Does it have an index on PATIENT_ID_1?
You might try a non-clustered index on the column pair (PATIENT_ID_1,
STATUS).
It probably will not perform any differently, but you could also try a
NOT EXISTS test in place of the IN.
SELECT count(*) as cntDuplicatePat,
FIRST_NAME, SURNAME, ADDRESS_1, POSTCODE, DATE_OF_BIRTH
FROM TBL_Patient as P
WHERE Merged_Into_ID is NULL
AND inactive='N'
AND NOT EXISTS
(SELECT * FROM TBL_PATIENT_DUPLICATES as D
WHERE P.PATIENT_ID = D.PATIENT_ID_1
AND STATUS <> 2)
GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
Roy Harvey
Beacon Falls, CT|||Thanks Roy!
I could get some stuff from your side. I also want to know how to
design a database so that query time is minimized and also the updates are
faster. can you prefer any e-book or any single url please.
Vikas
"Roy Harvey" wrote:
> On Wed, 2 Aug 2006 06:02:02 -0700, Vikas
> <Vikas@.discussions.microsoft.com> wrote:
> >Hi all, Iam new to DBA. I have tables with created indexes on all most all of
> >the columns. millions of records exists in this table. when i run the below
> >query, it is taking nearly 3 minutes to execute. How can i minimise the query
> >time ?
> Creating indexes on most of the columns is not usually the answer.
> Creating them on the right columns, and on the right sets of columns
> (with the columns in the right order) requires understanding of what
> queries will be doing.
> >SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
> >POSTCODE, DATE_OF_BIRTH
> > From TBL_Patient
> > where Merged_Into_ID is NULL AND inactive='N' and
> > PATIENT_ID NOT IN
> > (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> > GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> > HAVING (COUNT(*) >=2)
> >
> >Also i would like to know how a query time can be minimized (on what factors
> >the query time depends?)
> Using TOP without ORDER BY makes little sense. I would either drop
> the TOP 100, or add "ORDER BY cntDuplicatePat DESC".
> I would not expect indexing of TBL_Patient to help much with this
> query. The only way it might is if a very small percentage of
> patients were active, or a very small percentage satisfied the "
> Merged_Into_ID is NULL" test.
> However, indexing of TBL_PATIENT_DUPLICATES is another matter. How
> many rows are in TBL_PATIENT_DUPLICATES? How many columns? How many
> satisfy the test STATUS <> 2? Does it have an index on PATIENT_ID_1?
> You might try a non-clustered index on the column pair (PATIENT_ID_1,
> STATUS).
> It probably will not perform any differently, but you could also try a
> NOT EXISTS test in place of the IN.
> SELECT count(*) as cntDuplicatePat,
> FIRST_NAME, SURNAME, ADDRESS_1, POSTCODE, DATE_OF_BIRTH
> FROM TBL_Patient as P
> WHERE Merged_Into_ID is NULL
> AND inactive='N'
> AND NOT EXISTS
> (SELECT * FROM TBL_PATIENT_DUPLICATES as D
> WHERE P.PATIENT_ID = D.PATIENT_ID_1
> AND STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> Roy Harvey
> Beacon Falls, CT
>|||Sorry, there are lots of participants here with a catalog of links to
articles and books, but I'm not one of them.
The place to start in database design is normalization. Until you
understand that, and follow it, and do it by reflex, any other attempt
to design for minimized query time is probably going to create more
problems than it answers. Once you have that, then most of it is
proper indexing and queries.
Good luck!
Roy
On Wed, 2 Aug 2006 07:48:01 -0700, Vikas
<Vikas@.discussions.microsoft.com> wrote:
>Thanks Roy!
> I could get some stuff from your side. I also want to know how to
>design a database so that query time is minimized and also the updates are
>faster. can you prefer any e-book or any single url please.
>Vikas|||Vikas wrote:
> Hi all, Iam new to DBA. I have tables with created indexes on all most all of
> the columns. millions of records exists in this table. when i run the below
> query, it is taking nearly 3 minutes to execute. How can i minimise the query
> time ?
> SELECT TOP 100 count(*) as cntDuplicatePat, FIRST_NAME, SURNAME, ADDRESS_1,
> POSTCODE, DATE_OF_BIRTH
> From TBL_Patient
> where Merged_Into_ID is NULL AND inactive='N' and
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
> GROUP BY First_name, Surname, Address_1, Postcode, Date_of_birth
> HAVING (COUNT(*) >=2)
> Also i would like to know how a query time can be minimized (on what factors
> the query time depends?)
I'm curious about one part of your WHERE clause:
PATIENT_ID NOT IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
Isn't this the same as:
PATIENT_ID IN
(SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
Written the first way, you're most likely looking at an index or table
scan, whereas the second method will, assuming "status = 2" is selective
enough, use an index seek.
Also, if the second method is true, you might consider doing this as an
INNER JOIN instead of an IN.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Thu, 03 Aug 2006 08:55:52 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>I'm curious about one part of your WHERE clause:
> PATIENT_ID NOT IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
>Isn't this the same as:
> PATIENT_ID IN
> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
>Written the first way, you're most likely looking at an index or table
>scan, whereas the second method will, assuming "status = 2" is selective
>enough, use an index seek.
>Also, if the second method is true, you might consider doing this as an
>INNER JOIN instead of an IN.
Suppose there is NO row in TBL_PATIENT_DUPLICATES with a specific
PATIEND_ID. With the original version the NOT IN will be satisfied.
With the alternate version it will not be satisfied.
Roy Harvey
BeacoN Falls, CT|||Roy Harvey wrote:
> On Thu, 03 Aug 2006 08:55:52 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> I'm curious about one part of your WHERE clause:
>> PATIENT_ID NOT IN
>> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS <> 2)
>> Isn't this the same as:
>> PATIENT_ID IN
>> (SELECT PATIENT_ID_1 FROM TBL_PATIENT_DUPLICATES WHERE STATUS = 2)
>> Written the first way, you're most likely looking at an index or table
>> scan, whereas the second method will, assuming "status = 2" is selective
>> enough, use an index seek.
>> Also, if the second method is true, you might consider doing this as an
>> INNER JOIN instead of an IN.
> Suppose there is NO row in TBL_PATIENT_DUPLICATES with a specific
> PATIEND_ID. With the original version the NOT IN will be satisfied.
> With the alternate version it will not be satisfied.
> Roy Harvey
> BeacoN Falls, CT
Doh! Makes perfect sense, thanks...
Tracy McKibben
MCDBA
http://www.realsqlguy.com