MSSQL --ODBC-- ACCESS

[Verwijderd]

Beste lezer,

Ik ben bezig om een MS SQL database middels een ODBC koppeling binnen MS Access 2013 te leggen en zo doende een applicatie voor de database te maken.

De ODBC koppeling ligt er en werkt prima. Nu zit ik met het volgende:

Ik heb een aantal Queries opgesteld binnen MS SQL en de werken prima (bijna 400.000 records). Deze records worden binnen SQL Management Studio met deze query binnen 5 seconden opgevraagd.
Nu heb ik dezelfde query overgezet naar Access, maar access duurt er ipv 5 seconden (SQL Server) wel 3-4 minuten over dezelfde query.

Ik weet inmiddels dat min() en Max() binnen het select statement hier invloed op heeft. Zonder deze commando's laadt access de query in 2 seconden, en met 4-5 minuten.

Ik heb echt geen flauw idee hoe ik het anders zou moeten oplossen.


Onderstaand de link naar de query.


https://www.dropbox.com/s...tor-per%20record.sql?dl=0

[xleeuwx]

Ik heb eerst even je code netjes geformat

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ( Max(g.genre_name) + '/ ' + Min(g.genre_name) ) AS Genres,
       Max(d.lastname + ', ' + firstname)               AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price  

Verder staat de DB en MS access op de zelfde machine ?

Daarnaast zijn er indexes aangemaakt ?

[ Voor 6% gewijzigd door xleeuwx op 11-12-2014 13:44 ]

[Verwijderd]

xleeuwx schreef op donderdag 11 december 2014 @ 13:42:
Ik heb eerst even je code netjes geformat

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ( Max(g.genre_name) + '/ ' + Min(g.genre_name) ) AS Genres,
       Max(d.lastname + ', ' + firstname)               AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price  

Bedankt, ziet er inderdaad veel beter uit zo en ook makkelijker te troubleshooten. Ik ben zelf een chaoot wat betreft code


Edit: Ja, DB en Access op een en dezelfde machine. Indexes zijn er niet gemaakt.

[ Voor 4% gewijzigd door Verwijderd op 11-12-2014 13:45 ]

[xleeuwx]

A je kan gewoon je code hierin gooien en vervolgens hier bij tweakers de code tag gebruiken

code:

[code=sql][/code]

Hoe ik meestal troubleshoote is door mijn code in stukken te hakken.

Jij geeft aan dat dit dus binnen 3 a 5 sec moet werken via access toch ?

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price

dat betekend dus dat je probleem punt bij de select ligt of specifieker:

SQL:

( Max(g.genre_name) + '/ ' + Min(g.genre_name) ) AS Genres,
       Max(d.lastname + ', ' + firstname)               AS Director

[ Voor 80% gewijzigd door xleeuwx op 11-12-2014 13:50 . Reden: Stukje tekst toegevoegd ]

[Verwijderd]

Inderdaad, de onderste 2 regels code (max() min()), daar ligt het probleem. Zonder laadt Access dit binnen 2 seconden (instant eigenlijk), maar MET gaat het fout.

Heeft Access een andere methode om de query uit te voeren? Het lijkt erop dat die per record nog een de min en max gaat bekijken. Dit doet die dan * het totaal aantal records. Of zie ik dit verkeerd?

[xleeuwx]

Verwijderd schreef op donderdag 11 december 2014 @ 13:52:
Inderdaad, de onderste 2 regels code (max() min()), daar ligt het probleem. Zonder laadt Access dit binnen 2 seconden (instant eigenlijk), maar MET gaat het fout.

Heeft Access een andere methode om de query uit te voeren? Het lijkt erop dat die per record nog een de min en max gaat bekijken. Dit doet die dan * het totaal aantal records. Of zie ik dit verkeerd?

Je zou kunnen proberen om het met een sub query te doen. oftewel maak een select op de tabel en gebruik ORDER BY in combinatie met LIMIT 1

Zou je hem zo eens willen proberen ?

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ('' + Max(g.genre_name) + '/ ' + Min(g.genre_name)) AS Genres,
       ('' + d.lastname + ', ' + d.firstname) AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price 

edit: was even vergeten dat het MSSQL is (concat er weer even uitgehaald )

[ Voor 65% gewijzigd door xleeuwx op 11-12-2014 14:10 ]

[Verwijderd]

Msg 8120, Level 16, State 1, Line 6
Column 'directors.Lastname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'directors.Firstname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Dus ook de waardes in Concat moeten worden opgenomen in de GROUP BY. Als ik dat doe werkt het,maar dan klopt mijn uitkomst helaas niet meer.


EDIT: jou edit gezien , ga hem testen!

[ Voor 20% gewijzigd door Verwijderd op 11-12-2014 14:17 ]

[Verwijderd]

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ('' + Max(g.genre_name) + '/ ' + Min(g.genre_name)) AS Genres,
       ('' + MAX(d.lastname + ', ' + d.firstname) AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price 

Binnen SQL werkt die nu hetzelfde. Even testen met Access!
( de max was ook van toepassing op director_ID lastname en firstname.)

EDIT: Het werkt binnen Access, maar opnieuw rete traag. Jammer, je oplossing was wel goed!

[ Voor 10% gewijzigd door Verwijderd op 11-12-2014 14:23 ]

[xleeuwx]

Verwijderd schreef op donderdag 11 december 2014 @ 14:20:

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ('' + Max(g.genre_name) + '/ ' + Min(g.genre_name)) AS Genres,
       ('' + MAX(d.lastname + ', ' + d.firstname) AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id
       LEFT OUTER JOIN (movie_director md
                        INNER JOIN directors d
                                ON md.director_id = d.did)
                    ON m.movie_id = md.movie_id
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price 

Binnen SQL werkt die nu hetzelfde. Even testen met Access!
( de max was ook van toepassing op director_ID lastname en firstname.)

EDIT: Het werkt binnen Access, maar opnieuw rete traag. Jammer, je oplossing was wel goed!

Het is voor mij ook tijdje geleden dat ik met MsSQL moest werken (voornamelijk MySQL )

Zou je het volgende eens willen proberen ?

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ('' + Max(g.genre_name) + '/ ' + Min(g.genre_name)) AS Genres,
       (SELECT (d.lastname + ', ' + d.firstname) AS Director FROM directors INNER JOIN movie_director ON md.director_id = d.did WHERE .md.movie_ID = m.movie_id LIMIT 1) AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id       
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price 

Als dat beter werkt (sneller) dan zou je eventueel 2 subquery's kunnen maken voor zowel max(genre name) en min(genre name)

[CMD-Snake]

In mijn ervaring is Access als client voor SQL Server altijd traag. Ik heb het al een paar keer mee mogen maken, maar het was altijd niet vooruit te branden. Helaas ook nooit een oplossing voor kunnen vinden. Misschien dat je iets anders kan gebruiken?

Want moet Access ook een soort front end worden voor je gebruikers, misschien dat je nog een alternatief kan verzinnen? Bijvoorbeeld een webpagina?

[Verwijderd]

CMD-Snake schreef op donderdag 11 december 2014 @ 14:31:
In mijn ervaring is Access als client voor SQL Server altijd traag. Ik heb het al een paar keer mee mogen maken, maar het was altijd niet vooruit te branden. Helaas ook nooit een oplossing voor kunnen vinden. Misschien dat je iets anders kan gebruiken?

Want moet Access ook een soort front end worden voor je gebruikers, misschien dat je nog een alternatief kan verzinnen? Bijvoorbeeld een webpagina?

Het is eigenlijk een schoolproject voor de HAN. Officieel hoef ik deze functies niet te implementeren, maar ik doe graag iets extra's. Alles wil dat beetje extra niet echt lukken:)

[Verwijderd]

xleeuwx schreef op donderdag 11 december 2014 @ 14:29:
[...]


Het is voor mij ook tijdje geleden dat ik met MsSQL moest werken (voornamelijk MySQL )

Zou je het volgende eens willen proberen ?

SQL:

SELECT m.movie_id,
       m.title,
       m.rank,
       m.price,
       ('' + Max(g.genre_name) + '/ ' + Min(g.genre_name)) AS Genres,
       (SELECT (d.lastname + ', ' + d.firstname) AS Director FROM directors INNER JOIN movie_director ON md.director_id = d.did WHERE .md.movie_ID = m.movie_id LIMIT 1) AS Director
FROM   movie m
       LEFT OUTER JOIN (movie_genre mg
                        INNER JOIN genre g
                                ON mg.genre_name = g.genre_name)
                    ON m.movie_id = mg.movie_id       
GROUP  BY m.movie_id,
          m.title,
          m.rank,
          m.price 

Als dat beter werkt (sneller) dan zou je eventueel 2 subquery's kunnen maken voor zowel max(genre name) en min(genre name)

Hij doet het wel, maar ik heb nu bij alle films die worden getoond, dezelfde director haha. Dus ik zie maar 1 director in het gehele overzicht.

[xleeuwx]

Verwijderd schreef op donderdag 11 december 2014 @ 14:51:
[...]


Hij doet het wel, maar ik heb nu bij alle films die worden getoond, dezelfde director haha. Dus ik zie maar 1 director in het gehele overzicht.

Eu ja stom van mij subquery gaat hier natuurlijk niet werken

[Verwijderd]

xleeuwx schreef op donderdag 11 december 2014 @ 15:00:
[...]

Eu ja stom van mij subquery gaat hier natuurlijk niet werken

Ik ga nog even puzzelen. Hoop dat ik er nog uitkom.
EDIT: Uiteraard bedankt voor je hulp!!

[ Voor 7% gewijzigd door Verwijderd op 11-12-2014 15:23 ]

[xleeuwx]

Verwijderd schreef op donderdag 11 december 2014 @ 15:22:
[...]


Ik ga nog even puzzelen. Hoop dat ik er nog uitkom.
EDIT: Uiteraard bedankt voor je hulp!!

Graag gedaan, wat je nog zou kunnen proberen is de director / genre dingen in GROUP by opnemen en first gebruiken bij director inplaats van Max

[Verwijderd]

xleeuwx schreef op donderdag 11 december 2014 @ 15:25:
[...]

Graag gedaan, wat je nog zou kunnen proberen is de director / genre dingen in GROUP by opnemen en first gebruiken bij director inplaats van Max

In SQL Server is dat SELECT TOP 1. Maar dan krijg je inderdaad hetzelfde als ik net had bij jouw Query.

[Lustucru]

Qua snelheid: als je wilt dat de query op de server wordt uitgevoerd zul je hem moeten definieren als pass_ through. Ik gok erop dat je hem nu uitvoert tegen gekoppelde tabellen en dat is uiteraard traag. Eerst moeten alle data overgenomen worden en dan gaat pas de query door de ace/jet engine uitgevoerd worden.

[Verwijderd]

Lustucru schreef op donderdag 11 december 2014 @ 17:58:
Qua snelheid: als je wilt dat de query op de server wordt uitgevoerd zul je hem moeten definieren als pass_ through. Ik gok erop dat je hem nu uitvoert tegen gekoppelde tabellen en dat is uiteraard traag. Eerst moeten alle data overgenomen worden en dan gaat pas de query door de ace/jet engine uitgevoerd worden.

You sir, you deserve a medal.

Ik ging binnen Access naar de SQL view, maakte er een Pass Though van. De 1e keer werkte het, maar nadat ik terug ging naar het formulier, liep alles weer kut..
Nja, naar een beetje puzzelen, heb ik de gehele Query kunnen opslaan in Pass Though en vervolgens in de formulier eigenschappen deze Query gekozen.

Nu, het werkt als een zonnetje!

Ongelofelijk dat het werkt! IK ben zo ontzettend geholpen door iedereen!
Bedankt.

[F_J_K]

Het gaat hier mN. om de client. Dus sws-->csa.