UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Converting Vba Apps From SQL Server To Postresql, Access 2013    
 
   
hailstop
post Jul 20 2020, 03:58 PM
Post#1



Posts: 70
Joined: 1-September 08



Hi,

I'm running an Access front end currently using a SQL Server backend with linked tables. So far the migration is going okay, but there is one problem in particular that's causing me fits.

I have a SELECT INTO query that works but is taking a very long time, likely due to the multiple INNER JOINs. I've also tried doing a subquery but that doesn't seem to be working either.

We have indexes in the areas and area_forecasts tables, so I don't think that's the issue.

Here's the current query:


CODE
SELECT areas.areaname, areas.forecaster, area_forecasts.areaid AS area, area_forecasts.id AS areaforecastsid, area_forecasts.skycover, area_forecasts.precip, area_forecasts.comments, area_forecasts.snowlevel, area_forecasts.frostconcern AS f, area_forecasts.iceconcern AS i, area_forecasts.snowconcern AS s, area_forecasts.floodconcern AS fl, area_forecasts.windconcern AS w, area_forecasts.miscconcern1, area_forecasts.miscconcern2, area_forecasts.miscconcern3, area_forecasts.temp, area_forecasts.roadsnow, areas.active, area_forecasts.concomments, area_forecasts.rainfall, area_forecasts.wind, area_forecasts.timeframe, areas.con AS iscon, areas.isfis, areas.isw, areas.isfl, areas.domainname, areas.iswind, areas.israinfall, areas.order, areas.isbcrwisonlyday, areas.isbcrwisonlynight, areas.istimeframe3day, areas.istimeframe3night, areas.day0, areas.day1, areas.day2, areas.day3, areas.day4

FROM areas INNER JOIN area_forecasts ON areas.id = area_forecasts.areaid
WHERE (((areas.active = true)) AND ((area_forecasts.timeframe Between 2 and 5)) AND ((areas.forecaster = '" & UserName & "')) AND ((areas.domainname = 'British Columbia')))


Any ideas?
This post has been edited by hailstop: Jul 20 2020, 03:59 PM
Go to the top of the page
 
FrankRuperto
post Jul 20 2020, 04:17 PM
Post#2



Posts: 1,209
Joined: 21-September 14
From: Tampa, Florida USA


I don't understand the title of your post, as your problem doesn't have anything to do with converting from SQL-Server to Postresql.
Shouldn't the title be more like "Please Help With Long Running Query"?

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Jul 20 2020, 04:30 PM
Post#3



Posts: 3,845
Joined: 27-February 09



What indexes do you have on the tables? I'd index the join columns and the columns you're filtering on (well, if you're filtering from the left side, and not using something crazy like

SELECT ...
FROM tableA a
INNER JOIN tableB b
ON a.columnA = RIGHT(b.columnB,4)

Because b.columnB's index won't help. The function will cause a table scan, and those are really slow.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th August 2020 - 05:47 AM