quote:

---

Originally posted by tkizer

15 million rows in a table is small these days. You are probably missing indexes on your table if your queries are performing slow.

---

CREATE TABLE [dbo].[tbl_Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[sID] [int] NOT NULL, -- foreign key
	[sgID] [int] NOT NULL, -- foreign key
	[userID] [int] NOT NULL, -- foreign key
	[EventID] [int] NOT NULL, -- foreign key
	[newSkillLevel] [numeric](5, 2) NULL,
	[targetScore] [numeric](5, 2) NOT NULL,
	[comments] [nvarchar](max),
	[adminComments] [nvarchar](max),
	[dateAttained] [datetime] NOT NULL DEFAULT (getutcdate()),
 CONSTRAINT [PK_tbl_Test_1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[tbl_Test] ADD  CONSTRAINT [PK_tbl_Test_1] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [sgID_sID_uID] ON [dbo].[tbl_Test] 
(
	[sgID] ASC,
	[sID] ASC,
	[userID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

WHERE
  (sID = @sID) AND
  (sgID = @sgID) AND
  (userID = @userID OR @userID IS NULL) AND
  (eventID = @eventID OR @eventID IS NULL)

quote:

---

Originally posted by tkizer

Are there joins in your query referencing the foreign key tables? If so, are those columns indexed? SQL Server does not add indexes on foreign keys. SQL Server does do it for primary keys though. I make it a standard practice to add an index to all of my foreign keys.

---

quote:

---

Originally posted by GilaMonster

[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

---

quote:

---

Instead of using this form:

WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL

you should consider using this:

WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)

...I have found in my testing that this solution performs very well; indexes ARE used, performance is great, and dynamic code is not required.

---

WHERE 
	(ProductID = CASE WHEN @Product IS NULL THEN ProductID ELSE @Product END) AND 

quote:

---

Read the comments below that as well.

---

Nested Loops
(Inner Join)
Cost: 22%

Index Scan
[dbo].[tbl_Test]
Cost: 78%

Key Lookup
[dbo].[tbl_Test]
Cost: 0%

CREATE PROC myTest

 @sID int = NULL,
 @sgID int = NULL,
 @userID int = NULL,
 @eventID int = NULL

AS

 SELECT
     *
 FROM
     [myTest]
 WHERE
     (eventID = @eventID OR @eventID IS NULL) AND
     (sID = @sID OR @sID IS NULL) AND
     (sgID = @sgID OR @sgID IS NULL) AND
     (userID = @userID OR @userID IS NULL)

quote:

---

It's because of the catch-all query. SQL cannot index seek with a query of that form. Index scan is the best that it can do.

---

WHERE
     (sID = 500) AND
     (sgID = 600) AND
     (userID = 700)

IF @sID IS NOT NULL
BEGIN
   SELECT ...
   FROM ...
   WHERE
           (sID = @sID) 
       AND (@sgID IS NULL OR sgID = @sgID)
       AND (@userID IS NULL OR userID = @userID)   
END
ELSE
IF @sgID IS NOT NULL
BEGIN
   SELECT ...
   FROM ...
   WHERE
           sgID = @sgID
       AND (@userID IS NULL OR userID = @userID)   
END
ELSE ...