Extreem lange/trage query

[Verwijderd]

Ik ben op dit moment met een web-bases applicatie bezig welke informatie uit een microsoft sql server 2005 db moet halen. Nu zat ik met het probleem dat ik niet constant alle producten over de lijn wil hebben lopen dus zocht ik een manier om de producten die al eens eerder zijn gesynchroniseerd niet mee te nemen.

Nu heb ik eerst aan de applicatie-kant de productID's opgeslagen die ik al binnen heb, zodat ik deze in een query kan stoppen, maar dit wordt een te grote/lange/zware query voor sql , ik heb dit al geprobeerd met een not in(productID, productID) en een where clausule, productID <>.

Is dit op een andere en snellere manier te doen?

[urk_forever]

Wat je wilt heet paging. Zie bijvoorbeeld dit voorbeeld. Voor meer informatie Google

[leuk_he]

De productid die je al hebt in de applicatie opslaan in de database (winkelwagen of local temporary table?) zodat er er indexen gebruikt kunnen worden?

Verder heb je over een implementatieprobleem waar je niet stukjes van je implementatie laat zien. Dan moeten wel heel erg raden he? Waarschijnlijk zul je iets anders moeten aanpakken, maar je geeft te weinig gegevens hoe het nu werkt.

[Verwijderd]

leuk_he schreef op woensdag 02 juni 2010 @ 10:21:
De productid die je al hebt in de applicatie opslaan in de database (winkelwagen of local temporary table?) zodat er er indexen gebruikt kunnen worden?

Verder heb je over een implementatieprobleem waar je niet stukjes van je implementatie laat zien. Dan moeten wel heel erg raden he? Waarschijnlijk zul je iets anders moeten aanpakken, maar je geeft te weinig gegevens hoe het nu werkt.

Sorry zal het iets beter uitleggen.

Huidige situatie:
De klant heeft een microsoft sql db. Ik connect met deze database via php en haal producten op en stop deze in een php/mysql based webshop.

problemen: Bij elke synchronisatie moeten alle producten opgehaald worden.


Gewenste situatie: Alleen nieuwe producten ophalen uit de database. Op basis van bijv een exclude-tabel.

problemen: De exclude tabel heb ik in mysql aangemaakt en gevuld met fake-waarden. Hier komt een heel erg lange query uit: NOT IN (2500 * een productid). Deze query wordt niet uitgevoerd, ook niet in de sql managment studio.

*paging is geen optie omdat de productID's niet noodzakelijk opeenvolgend zijn.

[Laurens-R]

Ok dat is al iets meer, maar nu heb je nog steeds geen implementatie details laten zien.

Geef eens een voorbeeld query? Een stuk code? Nu weet ik alleen dat je NOT IN toepast, maar niet hoe en daar kan nou net wel het verschil in zitten

edit: je geeft ook aan dat de query niet wordt uitgevoerd... is de query dan uberhaubt wel goed?

[ Voor 51% gewijzigd door Laurens-R op 02-06-2010 10:35 ]

[Verwijderd]

EvilB2k schreef op woensdag 02 juni 2010 @ 10:30:
Ok dat is al iets meer, maar nu heb je nog steeds geen implementatie details laten zien.

Geef eens een voorbeeld query? Een stuk code? Nu weet ik alleen dat je NOT IN toepast, maar niet hoe en daar kan nou net wel het verschil in zitten

Dit is het voorbeeld van de query zoals ik hem zou willen uitvoeren:

code:

SELECT     product.productID AS productID
FROM         product INNER JOIN
                      groupInfo ON product.groepID = groupInfo .groepID
WHERE     (product.productid NOT IN ('13312', '21561', '27262', '27615', '4140', '31477', '12426', '26363', '792', '20849', 
                      '19286', '30679', '14532', '5037', '18146', '14963', '7472', '15785', '28462', '2767', '23644', '2917', '20538', '1259', '25160', '30945', '13830', '1223', '23284', '16925', 
                      '11410', '9827', '12896', '25369', '32734', '17855', '5260', '6101', '15338', '32475', '28024', '23633', '11446', '11703', '26916', '27789', '24130', '28243', '29584', 
                      '17545'))
ORDER BY productID

Deze query werkt wel, omdat het maar om 50 producten gaat in het not in gedeelte, maar dit kunnen er zomaar 2500 zijn.

De product tabel heeft een productID, productnaam en nog wat andere kolommen die verder geen relatie hebben met andere tabellen.

[d00d]

Misschien kun je een tijdelijke tabel (#product o.i.d.) aanmaken in SQL Server en daar een index op zetten en vervolgens een query schrijven als.

SQL:

SELECT ProductID FROM Product
EXCEPT
SELECT ProductID FROM #product;

[Laurens-R]

Wat nou als je nou een SYNC kolom toevoegd aan je productentabel (dit kan een bool zijn of een datetime)... dan ben je al klaar met een eenvoudige WHERE clause

(vb: WHERE SYNC = true of WHERE SYNCMOMENT > een bepaald moment in tijd)

of je nou een ID ophaalt voor de zaken die niet gesynced mogen worden, of een bitje gebruikt in de record zelf zou toch niet zoveel moeten uitmaken (zonder de verdere details van je implementatie te kennen).

[ Voor 32% gewijzigd door Laurens-R op 02-06-2010 10:48 ]

[Verwijderd]

EvilB2k schreef op woensdag 02 juni 2010 @ 10:46:
Wat nou als je nou een SYNC kolom toevoegd aan je productentabel (dit kan een bool zijn of een datetime)... dan ben je al klaar met een eenvoudige WHERE clause

(vb: WHERE SYNC = true of WHERE SYNCMOMENT > een bepaald moment in tijd)

Dit is helaas niet mogelijk omdat de software van de klant dan vastloopt (heb ik al geprobeerd ).

SELECT ProductID FROM Product
EXCEPT
SELECT ProductID FROM #product;

Tnx ga ik even proberen.

[urk_forever]

Verwijderd schreef op woensdag 02 juni 2010 @ 10:28:
[...]


Sorry zal het iets beter uitleggen.

Huidige situatie:
De klant heeft een microsoft sql db. Ik connect met deze database via php en haal producten op en stop deze in een php/mysql based webshop.

problemen: Bij elke synchronisatie moeten alle producten opgehaald worden.


Gewenste situatie: Alleen nieuwe producten ophalen uit de database. Op basis van bijv een exclude-tabel.

problemen: De exclude tabel heb ik in mysql aangemaakt en gevuld met fake-waarden. Hier komt een heel erg lange query uit: NOT IN (2500 * een productid). Deze query wordt niet uitgevoerd, ook niet in de sql managment studio.

*paging is geen optie omdat de productID's niet noodzakelijk opeenvolgend zijn.

Kijk dit geeft al wat meer informatie. Je geeft aan dat de productID's niet opeenvolgend zijn, maar zijn ze de id's van nieuwe producten wel altijd hoger als de producten die je al hebt? Dan zou je met een > operator de nieuwe producten op kunnen halen:

SQL:

SELECT     product.productID AS productID
FROM         product INNER JOIN
                      groupInfo ON product.groepID = groupInfo .groepID
WHERE     (product.productid > [het hoogste id wat je nu hebt]))
ORDER BY productID

edit:
Hiervoor zou je ook de ROW_NUMBER functie uit mijn link kunnen gebruiken. Je onthoudt het hoogste ROW_NUMBER en daarop filter je in je WHERE clausule

[ Voor 5% gewijzigd door urk_forever op 02-06-2010 10:59 ]

[Verwijderd]

urk_forever schreef op woensdag 02 juni 2010 @ 10:54:
[...]


Kijk dit geeft al wat meer informatie. Je geeft aan dat de productID's niet opeenvolgend zijn, maar zijn ze de id's van nieuwe producten wel altijd hoger als de producten die je al hebt? Dan zou je met een > operator de nieuwe producten op kunnen halen:

SQL:

SELECT     product.productID AS productID
FROM         product INNER JOIN
                      groupInfo ON product.groepID = groupInfo .groepID
WHERE     (product.productid > [het hoogste id wat je nu hebt]))
ORDER BY productID

edit:
Hiervoor zou je ook de ROW_NUMBER functie uit mijn link kunnen gebruiken. Je onthoudt het hoogste ROW_NUMBER en daarop filter je in je WHERE clausule

Nee, dat is jammer het kan dus zo zijn dat wanneer id 1000 net in de excludelist staat, dat id 3 de dag daarna in de exclude list komt. Het is een beroerd systeem, maar ik kan/mag/wil er niets aan veranderen

[Laurens-R]

Wellicht met je klant om de tafel gaan zitten en het even hebben over verwachtingen versus realiteit?

Komt op mij nl. over als een niet al te intelligent ontworpen pakket, gezien de gevolgen die simpele wijzigingen blijkbaar hebben. Of het zijn de constraints die je door de klant worden opgelegd die enorm beperkend werken.

(Als consultant word je tenslotte ingehuurd om kritisch naar heilige huisjes te kijken)

[ Voor 28% gewijzigd door Laurens-R op 02-06-2010 11:19 ]

[Janoz]

Deze oplossing gaat zoals je zelf al hebt gemerkt niet zo best werken. Waarschijnlijk is het handiger om een andere oplossing te kiezen. Je zou bijvoorbeeld bij kunnen houden wanneer producten hun laatste wijziging gehad hebben. Op dat moment hoef je alleen maar bij te houden wanneer je laatste synchronisatie was. Om alle wijzigingen op te vragen hoef je alleen maar alle producten op te vragen die na dat moment gewijzigd zijn.

Het enige wat dan niet werkt is producten die verwijderd zijn, maar dat werkt met je huidige oplossing ook niet.


--edit
Hmm.. stom. Misschien eerst even op F5 drukken voordat ik wat ga posten na het bellen

[ Voor 8% gewijzigd door Janoz op 02-06-2010 11:30 ]

[Verwijderd]

EvilB2k schreef op woensdag 02 juni 2010 @ 11:17:
Wellicht met je klant om de tafel gaan zitten en het even hebben over verwachtingen versus realiteit?

Komt op mij nl. over als een niet al te intelligent ontworpen pakket, gezien de gevolgen die simpele wijzigingen blijkbaar hebben. Of het zijn de constraints die je door de klant worden opgelegd die enorm beperkend werken.

(Als consultant word je tenslotte ingehuurd om kritisch naar heilige huisjes te kijken)

Ja, daar heb je gelijk in maar dat krijg ik er niet door. Het moet op dit moment een werkend paket zijn en er is verder geen budget. Ik hou zelf ook niet van pleisters plakken maar soms kun je niet anders

[leuk_he]

Verwijderd schreef op woensdag 02 juni 2010 @ 10:28:
[...]

Huidige situatie:
De klant heeft een microsoft sql db. Ik connect met deze database via php en haal producten op en stop deze in een php/mysql based webshop.

problemen: De exclude tabel heb ik in mysql aangemaakt en gevuld met fake-waarden. Hier komt een heel erg lange query uit: NOT IN (2500 * een productid).

Je zult die temporary tabel moeten aanmaken in de MS database he? en daar een goede index op leggen. Of je legt een vlaggetje aan in de MSSQL database "processed" of een datamodified column bijhouden in de MSSQL database.

Als je in een master database gaat vragen: geef me alles ... behalve x,y,z dan loop je tegen grenzen aan. Dat moet je in de master database regelen. Of je moet een volledige download doen en dan de diffs in de webshop mysql doen.

[Verwijderd]

leuk_he schreef op woensdag 02 juni 2010 @ 14:15:
[...]


Je zult die temporary tabel moeten aanmaken in de MS database he? en daar een goede index op leggen. Of je legt een vlaggetje aan in de MSSQL database "processed" of een datamodified column bijhouden in de MSSQL database.

Als je in een master database gaat vragen: geef me alles ... behalve x,y,z dan loop je tegen grenzen aan. Dat moet je in de master database regelen. Of je moet een volledige download doen en dan de diffs in de webshop mysql doen.

Klopt ik ben nu bezig de implementatie te wijzigen door het in een tabel te zetten en daarmee de query uit te voeren.

De andere 2 opties zijn niet mogelijk vanwege het bestaande programmeerwerk.

Bedankt voor de tips everyone

[kluyze]

Je spreekt over 2500 producten, hoeveel kunnen dit dan zijn in het totaal? Is er een reden dat je die 2500 producten niet steeds wilt opvragen?

Over wat voor data spreken we dan? Staan er zware xml strings of grote blob's in die database? Kortom over hoeveel MB spreken we en hoe groot is dat indien je een compressie op je connectie gebruikt? Is het het waard om allerlei fratsen uit te halen of kan je niet gewoon beter telkens alles uit halen?
En hoe dikwijls ga je die database moeten aanspreken? Je spreekt over de data overzetten naar jouw database, dus 1 keer per dag? Of elke keer dat iemand data opvraagt? Veranderd die externe database zo snel?

Kan je verder ook niets doen met een insert date? Staat er een insert date veld op die database? Kan je dan niet zeggen dat je alleen de data wilt die vandaag geïnserteerd is?

[Black Hawk]

Misschien nog een ander idee (met in het achterhoofd dat je in de master-db niets kan/mag/wil wijzigen en een pleister-oplossing zoekt): draai 2 queries per synchronisatie.
De eerste query haalt alleen de productID's op (dus zonder die INNER JOIN). Deze ID's vergelijk je met je lokale db.
Degene die je nog niet hebt vraag je dan met een tweede query op (SELECT .. WHERE ProductID IN( __hier de ID's van de nieuwe producten __ ).

Zoals ik al zei, dit is dus een pleister-oplossing. En ik ben er vanuit gegaan dat er per sync-moment niet al te veel producten zullen worden gewijzigd (anders wordt je 2e query ook weer te lang).

[koenie]

Twee vragen:
1. Waarom kan je niet rechtsstreeks op de MS SQL server je website draaien? Als alle gegevens immer er al zijn dan lijkt me het introduceren van een extra database als MySQL nogal overbodig.
2. Mocht dit om een of andere reden niet mogelijk zijn, waarom maak je dan geen gebruik van het BI pakket SSIS dat geleverd is bij de MS SQL server? Hiermee is het zeer eenvoudig om data te synchroniseren tussen verschillende databases

[roy-t]

Hele gekke oplossing: geef een kolom mee waarin staat wanneer een item is toegevoegd / gewijzigd. Sla de datum+tijd op van de laatste synchronisatie en de volgende synchronisatie doe je een select * where dataChanged > prevSyncDate.

Of denk ik nu veeel te simpel?

[Gomez12]

roy-t schreef op zaterdag 12 juni 2010 @ 17:22:
Hele gekke oplossing: geef een kolom mee waarin staat wanneer een item is toegevoegd / gewijzigd. Sla de datum+tijd op van de laatste synchronisatie en de volgende synchronisatie doe je een select * where dataChanged > prevSyncDate.

Of denk ik nu veeel te simpel?

Werkt perfect als je een simpele tabel hebt met daarin je producten, heb je geen dataChanged dan kan je een trigger oid op de dbase zetten die het toevoegt.

Maar, als je een iets genormaliseerder model hebt dan wordt het al snel heel erg ingewikkeld. 1 plaatje veranderen kan leiden tot 200 producten die veranderd zijn, nieuw plaatje uploaden met dezelfde naam leidt tot nog meer chaos...
Een item heeft veelal weer attributen die weer los kunnen wijzigen. Zonder dat het item zelf wijzigt.

P.s. de huidige methodiek van de TS pakt deze problemen ook niet aan, dus in wezen zou je een trigger op de producttabel kunnen zetten die bij een update / insert / delete een datachanged vult. Dan ben je net zo ver als TS nu is.

[PolarBear]

koenie schreef op zaterdag 12 juni 2010 @ 17:21:
Twee vragen:
1. Waarom kan je niet rechtsstreeks op de MS SQL server je website draaien? Als alle gegevens immer er al zijn dan lijkt me het introduceren van een extra database als MySQL nogal overbodig.
2. Mocht dit om een of andere reden niet mogelijk zijn, waarom maak je dan geen gebruik van het BI pakket SSIS dat geleverd is bij de MS SQL server? Hiermee is het zeer eenvoudig om data te synchroniseren tussen verschillende databases

Met hem! SSIS is ook voor dit soort vraagstukken bedoelt (het is niet direct een BI pakket, dat is AS en RS).

[roy-t]

Gomez12 schreef op zaterdag 12 juni 2010 @ 17:36:
[...]

Werkt perfect als je een simpele tabel hebt met daarin je producten, heb je geen dataChanged dan kan je een trigger oid op de dbase zetten die het toevoegt.

Maar, als je een iets genormaliseerder model hebt dan wordt het al snel heel erg ingewikkeld. 1 plaatje veranderen kan leiden tot 200 producten die veranderd zijn, nieuw plaatje uploaden met dezelfde naam leidt tot nog meer chaos...
Een item heeft veelal weer attributen die weer los kunnen wijzigen. Zonder dat het item zelf wijzigt.

P.s. de huidige methodiek van de TS pakt deze problemen ook niet aan, dus in wezen zou je een trigger op de producttabel kunnen zetten die bij een update / insert / delete een datachanged vult. Dan ben je net zo ver als TS nu is.

Hmm ja dan zou je bij elke tabel zo'n kolom moeten toevoegen, ook niet handig natuurlijk. Ik zie je punt!