asked on 12/6/2019

sql get all existing values??

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

SQL

Last Comment

PortletPaul

12/7/2019

Pavel Celba

12/6/2019

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 Pletcher

12/6/2019

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.

ASKER CERTIFIED SOLUTION

PortletPaul

12/7/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial