We help IT Professionals succeed at work.

sql get all existing values??

elucero
elucero used Ask the Experts™
on
what can I do instead of the following?  It takes too long.

--get all values
select  distinct
a.[Year],
b.Region,
b.County
from
(select distinct [year] from [dbo].[YTDDate] as a with(nolock)) a,
(select distinct Region, County from [dbo].[Geographics] as b with(nolock) ) b
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Which part of the query takes too long?
select distinct [year] from [dbo].[YTDDate] as a with(nolock)
or
select distinct Region, County from [dbo].[Geographics] as b with(nolock)
?
How many rows is it returning? How many rows are in the original tables?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You can remove the distinct from the outer query, like so:

select
a.[Year],
b.Region,
b.County
from
(select distinct [year] from [dbo].[YTDDate] as a with(nolock)) a,
(select distinct Region, County from [dbo].[Geographics] as b with(nolock) ) b

If that doesn't improve the speed enough, add this index on the Geographics table:

create nonclustered index [Geographics__ix_region_county] on dbo.Geographics ( Region, County ) with ( fillfactor = 95, sort_in_tempdb = on );

Then try the original code again.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
You could bypass the first subquery by nominating some needed values instead:
SELECT
    y.[Year]
  , rc.Region
  , rc.County
FROM (
    SELECT
        2016 AS [year]
    UNION ALL
    SELECT
        2017 AS [year]
    UNION ALL
    SELECT
        2018 AS [year]
    UNION ALL
    SELECT
        2019 AS [year]
) y
CROSS JOIN (
        SELECT DISTINCT
            Region
          , County
        FROM [dbo].[Geographics] AS b WITH (NOLOCK)
    ) rc

Open in new window

although it probably won't make much difference.