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    
post Jul 20 2020, 03:58 PM

Posts: 70
Joined: 1-September 08


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:

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
post Jul 20 2020, 04:17 PM

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
post Jul 20 2020, 04:30 PM

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

FROM tableA a
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