Anybody a SQL Server guru?

[blockerdave]

Hi,

I am building a website/app, i have a particular SQL Server query that is causing me a problem.

I think i know the basis of the issue - joins - but i'm struggling to resolve it.

Basically I would show a timeline, that only shows posts of you, or other entities in the system you follow (it's not so simple as to say other users)...

Unfortunately, my query is returning also posts of users that follow you, even if you don't follow them.

if anyone is an expert in SQL server, perhaps they wouldn't mind me to DM them proper details including the query and they could help me resolve it?

 

[Abhas]

I can try. Message me.
I have experience with MySQL, and did a bit of SQL Server long time back too. As long as it's not a syntax problem, the basic nature of the query is similar for both.

 

[blockerdave]

I can try. Message me.
I have experience with MySQL, and did a bit of SQL Server long time back too. As long as it's not a syntax problem, the basic nature of the query is similar for both.

Thanks! I'll wing it over.

My hunch is it's one of two things:

either the way i've structured my tables; or the joins.

or possibly both!

 

[MattW]

I just use convoluted WHERE .. IN (SELECT ...) statements in places you should usually use joins - I know it's usually wrong and performs awfully, but I have more CPU power than SQL skills so it usually doesn't matter.

 

[Abhas]

I just use convoluted WHERE .. IN (SELECT ...) statements in places you should usually use joins - I know it's usually wrong and performs awfully, but I have more CPU power than SQL skills so it usually doesn't matter.

Joins are very powerful, and can save a lot of time if you can use them properly. They are easy to accomplish if the database is structured well. The usual problem people face with joins are understanding when to Left, Right, or inner join.

 

[zimrahil]

Matt clearly not angling for developer role at BA

 

[blockerdave]

I just use convoluted WHERE .. IN (SELECT ...) statements in places you should usually use joins - I know it's usually wrong and performs awfully, but I have more CPU power than SQL skills so it usually doesn't matter.

ideally this would be a public website, so performance is an issue[DOUBLEPOST=1444418064][/DOUBLEPOST]

Joins are very powerful, and can save a lot of time if you can use them properly. They are easy to accomplish if the database is structured well. The usual problem people face with joins are understanding when to Left, Right, or inner join.

haven't had a chance to try your suggestions yet, hopefully later tonight or tomorrow morning.

 

[Abhas]

haven't had a chance to try your suggestions yet, hopefully later tonight or tomorrow morning.

Do let me whenever you try it. At the moment I can't see anything else wrong.

 

[blockerdave]

the where clause you suggested wasn't quite right, but i think it's certainly put me on to the solution with a couple of tweaks to the structure you suggested.

just need to add some more data to check that it's definitely giving the right result.

 

[Abhas]

I just use convoluted WHERE .. IN (SELECT ...) statements in places you should usually use joins - I know it's usually wrong and performs awfully, but I have more CPU power than SQL skills so it usually doesn't matter.

I remember, in one of our first web-apps, there was a query we were executing which clearly required joins, but the amateur developer I had used loops in php to achieve the same effect.
At the peak (when I decided to make a change), the report was loading in 17-18 minutes.

On changing the query by using joins, and removing all loops from PHP, we managed to reduce the page load time to less than 30 seconds.