I'm trying to find a more performance way to search a log table. The table logs all searches performed on a site, and can contain multiple filters on a single criteria. For example, users can search for homes in multiple counties, and multiple property types on a single search. I need to be able to run a report to find how many users searched within a specific county/counties with a specific property type/types. The searches are currently logged in the following tables:
Stores the dimension definitions for a search:
CREATE TABLE [dbo].[LogSearchDimensions](
[ID] [int] IDENTITY(1,1) NOT NULL,
[VarName] [nvarchar](50) NOT NULL,
[Label] [nvarchar](50) NOT NULL,
[Description] [nvarchar](1024) NOT NULL,
[Created] [datetime] NOT NULL,
CONSTRAINT [PK_LogSearchDimensions] 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]
Example data:
ID VarName Label
----------- -------------------------------------------------- --------------------------------------------------
3 City_ID City ID
5 County_ID County ID
7 PageNum Page Number
8 PriceLow Lowest Price
9 PriceHigh Highest Price
10 Region_ID Region ID
11 Site_ID Site ID
14 AcreLow Lowest Acreage
15 AcreHigh Highest Acreage
16 State_ID State ID
17 Style Style
18 SiteStateID Site State ID
19 Distance Distance
20 FIPS FIPS Code
Stores the primary search information, such as when a search was performed, and who performed it:
CREATE TABLE [dbo].[LogSearches](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[RecordCount] [int] NOT NULL,
[PageNumber] [int] NOT NULL,
[IPAddress] [varchar](15) NOT NULL,
[Domain] [nvarchar](150) NOT NULL,
[ScriptName] [nvarchar](500) NOT NULL,
[QueryString] [varchar](max) NULL,
[Referer] [nvarchar](1024) NOT NULL,
[SearchString] [nvarchar](max) NOT NULL,
[UserAgent] [nvarchar](2048) NULL,
[Processed] [datetime] NOT NULL,
[Created] [datetime] NOT NULL,
[IntegerIP] [int] NULL,
CONSTRAINT [PK_LogSearches] 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]
Stores the dimensions for each search. This could be a single record, or 50, depending on the search that was performed:
CREATE TABLE [dbo].[LogSearchesDimensions](
[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[LogSearch_ID] [numeric](18, 0) NOT NULL,
[LogSearchDimension_ID] [int] NOT NULL,
[SearchValue] [bigint] NULL,
CONSTRAINT [PK_LogSearchesDimensions] 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]
GO
ALTER TABLE [dbo].[LogSearchesDimensions] WITH CHECK ADD CONSTRAINT [FK_LogSearchesDimensions_LogSearchDimensions] FOREIGN KEY([LogSearchDimension_ID])
REFERENCES [dbo].[LogSearchDimensions] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LogSearchesDimensions] CHECK CONSTRAINT [FK_LogSearchesDimensions_LogSearchDimensions]
GO
ALTER TABLE [dbo].[LogSearchesDimensions] WITH CHECK ADD CONSTRAINT [FK_LogSearchesDimensions_LogSearches] FOREIGN KEY([LogSearch_ID])
REFERENCES [dbo].[LogSearches] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[LogSearchesDimensions] CHECK CONSTRAINT [FK_LogSearchesDimensions_LogSearches]
GO
In the LogSearchesDimensions table, I could have multiple records for County_ID (LogSearchDimension_id 5) if the user searched for more than one county in a single search. Let's assume the user searched in counties 5, 6, 7, 12, and 15. When I'm running a report, this single search would need to show up in the reports for all 5 counties that were searched. If I ran a report that combined counties 5 and 6, then it should only show once.
I know this is a lot of information, and probably still not enough, but I'm hoping someone who's done similar could share some tips for making this type of filter work with some degree of speed.
I currently have a very complicated query, with all sorts of joins and having conditions to try and find searches with the proper number of matches, but it's not performing well at all. I've attached a diagram of the tables to show the relationships.
I'm currently searching using the following methods:
CREATE VIEW [dbo].[vwLogSearchesCounty]
AS
SELECT dbo.LogSearches.ID, CAST(FLOOR(CAST(dbo.LogSearches.Created AS FLOAT)) AS DATETIME) AS Created, C.county, C.County_ID, S.state, S.state_ID
FROM dbo.LogSearches WITH (NOLOCK) INNER JOIN
dbo.LogSearchesDimensions AS D WITH (NOLOCK) ON dbo.LogSearches.ID = D.LogSearch_ID AND D.LogSearchDimension_ID = 5 INNER JOIN
propertyControlCenter.dbo.county AS C WITH (NOLOCK) ON C.County_ID = D.SearchValue INNER JOIN
propertyControlCenter.dbo.state AS S WITH (nolock) ON C.state_ID = S.state_ID
DECLARE @LowDate DATETIME, @HighDate DATETIME;
SET @LowDate = '2010-01-01' ;
SET @HighDate = '2010-02-01' ;
SELECT
CONVERT(varchar, Created, 107) AS displayDate
, County , County_ID
, reportCount
FROM (
SELECT
Created
, County_ID , County , reportCount
, DENSE_RANK() OVER(ORDER BY MaxRecords DESC, County) AS theRank
FROM (
SELECT
v.County_ID
, v.Created
, C.county + ' County, ' + S.State_Code AS County
, COUNT(DISTINCT v.ID) AS reportCount
, MAX(COUNT(DISTINCT v.ID)) OVER(PARTITION BY v.County_ID) AS MaxRecords
FROM
vwLogSearchesCounty v (NOLOCK)
INNER JOIN propertyControlCenter.dbo.county C (NOLOCK) ON
v.County_ID = C.County_ID
AND v.Created BETWEEN @LowDate AND @HighDate
AND c.State_ID = 48
INNER JOIN propertyControlCenter.dbo.state S (NOLOCK) ON C.state_ID = S.state_ID
INNER JOIN LogSearchesDimensions D (NOLOCK) ON v.ID = D.LogSearch_ID AND D.LogSearchDimension_ID IN (8, 9, 14, 15, 17)
WHERE
1 = 0
OR (
D.LogSearchDimension_ID = 17
AND D.SearchValue IN (2,5,6 )
)
GROUP BY
v.Created
, v.County_ID
, C.County
, S.State_Code
HAVING COUNT(v.ID) >= 3 ) d
GROUP BY
Created
, County
, County_ID
, reportCount
, MaxRecords )
ranking
WHERE theRank <= 5
ORDER BY theRank, County_ID, Created
Current record counts: LogSearches: 8,970,000 LogSearchesDimensions: 37,630,000
The above query takes 24 seconds to run (too long for our purposes) and returns the following data:
displayDate County County_ID reportCount
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
Jan 01, 2010 Bastrop County, TX 6218 49
Jan 02, 2010 Bastrop County, TX 6218 84
Jan 03, 2010 Bastrop County, TX 6218 76
Jan 04, 2010 Bastrop County, TX 6218 118
Jan 05, 2010 Bastrop County, TX 6218 92
Jan 06, 2010 Bastrop County, TX 6218 59
Jan 07, 2010 Bastrop County, TX 6218 45
Jan 08, 2010 Bastrop County, TX 6218 84
Jan 09, 2010 Bastrop County, TX 6218 71
Jan 10, 2010 Bastrop County, TX 6218 91
Jan 11, 2010 Bastrop County, TX 6218 67
Jan 12, 2010 Bastrop County, TX 6218 52
Jan 13, 2010 Bastrop County, TX 6218 76
Jan 14, 2010 Bastrop County, TX 6218 104
Jan 15, 2010 Bastrop County, TX 6218 69
Jan 16, 2010 Bastrop County, TX 6218 51
Jan 17, 2010 Bastrop County, TX 6218 105
Jan 18, 2010 Bastrop County, TX 6218 76
Jan 19, 2010 Bastrop County, TX 6218 72
Jan 20, 2010 Bastrop County, TX 6218 69
Jan 21, 2010 Bastrop County, TX 6218 32
Jan 22, 2010 Bastrop County, TX 6218 54
Jan 23, 2010 Bastrop County, TX 6218 60
Jan 24, 2010 Bastrop County, TX 6218 76
Jan 25, 2010 Bastrop County, TX 6218 95
Jan 26, 2010 Bastrop County, TX 6218 73
Jan 27, 2010 Bastrop County, TX 6218 64
Jan 28, 2010 Bastrop County, TX 6218 57
Jan 29, 2010 Bastrop County, TX 6218 41
Jan 30, 2010 Bastrop County, TX 6218 87
Jan 31, 2010 Bastrop County, TX 6218 67
Feb 01, 2010 Bastrop County, TX 6218 66
Jan 01, 2010 Montgomery County, TX 6199 51
Jan 02, 2010 Montgomery County, TX 6199 70
Jan 03, 2010 Montgomery County, TX 6199 69
Jan 04, 2010 Montgomery County, TX 6199 74
Jan 05, 2010 Montgomery County, TX 6199 44
Jan 06, 2010 Montgomery County, TX 6199 60
Jan 07, 2010 Montgomery County, TX 6199 37
Jan 08, 2010 Montgomery County, TX 6199 39
Jan 09, 2010 Montgomery County, TX 6199 40
Jan 10, 2010 Montgomery County, TX 6199 71
Jan 11, 2010 Montgomery County, TX 6199 63
Jan 12, 2010 Montgomery County, TX 6199 54
Jan 13, 2010 Montgomery County, TX 6199 51
Jan 14, 2010 Montgomery County, TX 6199 46
Jan 15, 2010 Montgomery County, TX 6199 54
Jan 16, 2010 Montgomery County, TX 6199 45
Jan 17, 2010 Montgomery County, TX 6199 73
Jan 18, 2010 Montgomery County, TX 6199 70
Jan 19, 2010 Montgomery County, TX 6199 30
Jan 20, 2010 Montgomery County, TX 6199 57
Jan 21, 2010 Montgomery County, TX 6199 59
Jan 22, 2010 Montgomery County, TX 6199 43
Jan 23, 2010 Montgomery County, TX 6199 49
Jan 24, 2010 Montgomery County, TX 6199 72
Jan 25, 2010 Montgomery County, TX 6199 86
Jan 26, 2010 Montgomery County, TX 6199 43
Jan 27, 2010 Montgomery County, TX 6199 69
Jan 28, 2010 Montgomery County, TX 6199 57
Jan 29, 2010 Montgomery County, TX 6199 46
Jan 30, 2010 Montgomery County, TX 6199 52
Jan 31, 2010 Montgomery County, TX 6199 107
Feb 01, 2010 Montgomery County, TX 6199 40
Jan 01, 2010 Fayette County, TX 6240 58
Jan 02, 2010 Fayette County, TX 6240 65
Jan 03, 2010 Fayette County, TX 6240 50
Jan 04, 2010 Fayette County, TX 6240 61
Jan 05, 2010 Fayette County, TX 6240 52
Jan 06, 2010 Fayette County, TX 6240 48
Jan 07, 2010 Fayette County, TX 6240 44
Jan 08, 2010 Fayette County, TX 6240 40
Jan 09, 2010 Fayette County, TX 6240 25
Jan 10, 2010 Fayette County, TX 6240 56
Jan 11, 2010 Fayette County, TX 6240 51
Jan 12, 2010 Fayette County, TX 6240 47
Jan 13, 2010 Fayette County, TX 6240 43
Jan 14, 2010 Fayette County, TX 6240 47
Jan 15, 2010 Fayette County, TX 6240 43
Jan 16, 2010 Fayette County, TX 6240 37
Jan 17, 2010 Fayette County, TX