1
vote
0answers
52 views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
0
votes
1answer
50 views

CROSS APPLY versus INNER JOIN

I was reading a lot about cross apply versus inner join recently. I tried writing some query and they both work kinda same, execution seem to be same aswell. Maybe someone with a deeper knowledge ...
0
votes
1answer
35 views

Query Rows That Have a Following Row That Meets Criteria

Will try to do my best to explain this, as I don't really know how to word this to search for an answer. I'm a constantly learning but still newish SQL user who thus far has been able to solve most ...
1
vote
1answer
73 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
2
votes
1answer
73 views

If an INNER JOIN in a query returns no rows, do additional INNER JOINS add a significant performance overhead?

If I make a query as follows: SELECT * FROM TableA A INNER JOIN TableB B ON A.AID = B.AID INNER JOIN TableC C ON B.BID = C.BID Does the INNER JOIN with TableC add significantly to the performance ...
0
votes
1answer
52 views

Best Practice For Data Views with Linked Server in SQL Server

In SQL Server, I have to design a View that I can map on a DBML file in order to dynamically retrieve information from the database with multiple conditions to filter. The problem is that I have to ...
1
vote
1answer
50 views

Get data per Customer and per Date

How will I get the all the date even though there is no order between two dates? Sample Table OrderID | Date | CusID 33942 | 6-21-2014 | 6005 34059 | 6-20-2014 | 4003 53333 | ...
0
votes
0answers
66 views

Are heterogeneous joins between MS Access and SQL Server tables really so bad?

I'm developing a multi-user Access 2013 application with an Access backend on a fileserver. At first I'm going to finish my stable Access application which works fine for the moment. However, I plan ...
0
votes
2answers
36 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
2
votes
1answer
41 views

Update only the oldest entry in a table with foreign key criteria

I'm working on a forum website. What I am trying to do is updating the thread Title and Content (first post), which resides in different tables - "Threads" and "Posts". "Threads" "Posts" I'm ...
11
votes
1answer
260 views

Why does subquery use parallelism and join doesn't?

Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete. Join ...
1
vote
1answer
101 views

How to perform a multiplication by a different select query

Hope my question is accepted here and I haven't made a mess out of renaming the tables and columns so you can understand what I need! :) Query A: DECLARE @var VARCHAR(7) SET @var = 'xtpo' SELECT ...
4
votes
2answers
2k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
4
votes
1answer
3k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
1
vote
3answers
973 views

Conditional Join - Specific Column Select

I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...) Then say that I have conditional join like this: select isnull(Emp.Full_Name, ...
0
votes
1answer
144 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
1
vote
2answers
437 views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
4
votes
4answers
301 views

Table join to match without duplicates

I have a situation where I need to match arrivals and departures of assets in a location. The problem is that asset movements are not always entered into the database chronologically, nor is there ...
-3
votes
1answer
115 views

SQL Server: Month Range Join [closed]

Given that there are two dates like 2012-07-08 and 2013-02-06. I need to count record of each user for every month. If there is no data for a user in particular month then it should return zero. The ...
3
votes
1answer
389 views

Forcing Join Order

I have a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID ...
0
votes
1answer
212 views

Simplify subqueries with join

I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data. ...
1
vote
1answer
43 views

Joining data from legacy database

I'm trying to figure out how to write this query. Table 1 - Booking BOOKING_ID | BOOKING_DATE | etc... 1 | 2013/01/01 | 2 | 2013/01/01 | Table 2 - BOOKING_PROCEDURE_TYPE ...
1
vote
3answers
276 views

Will performance be better if I specify a filter multiple times?

Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN? To clarify using code: select * from #main_select left outer join ( select main_id ...
7
votes
3answers
2k views

Splitting SQL query with many joins into smaller ones helps?

We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
4
votes
2answers
713 views

How can row estimates be improved in order to reduce chances of spills to tempdb

I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
-2
votes
3answers
158 views

Left Join not giving desired result

I have two Datasets FEES PAYMENTS CrsCode InstNo FEE Regno CRSCODE Instno Payment CA1 -2 100 R1 CA1 -2 100 CA1 -1 200 ...
0
votes
1answer
44 views

Getting the name of the column that was used in a join?

This may be a bit of a weird question: Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get ...
0
votes
1answer
106 views

Why is there such a huge performance difference in these two join statements?

I have a query which i first wrote as (query has been simplified a bit) : select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c inner join product p on p.indID = ...
1
vote
1answer
117 views

Join Calender Table

I have a Datatable and a Calendartable. I do a join on this tables to get sequential dates if there is no data for this day. My problem now is that there a several id's and i need a count for each ...
3
votes
2answers
637 views

Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions. We need ...
2
votes
2answers
109 views

Joining 4 tables and 2 counts

I have four tables. UserMaster ---------- UserKey - PK UserName UserID - UNIQUE UserLogTransaction ------------------ UserID - FK:UserMaster.UserID LoginTime LogoutTime CallCenter ---------- ...
-3
votes
3answers
2k views

Join tables on a partial string match

input: I have a table containing (any number of) user-supplied strings: ╔═══════╗ ║ Value ║ ╠═══════╣ ║ To ║ ║ An ║ ╚═══════╝ And a table of user details: ╔════════╦══════════╗ ║ UserID ║ ...
3
votes
2answers
585 views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order ...
1
vote
0answers
160 views

Multi-Table Join to Single Destination

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
1
vote
1answer
181 views

Querying multiple tables

I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together. I have the following tables/fields: SR_service: stores ticket ...
4
votes
1answer
390 views

Build a three table join with a recusive table in the middle?

I have three relevent tables: Parts, PartGroup, and MarkupGroup. Parts is simple. PartID artificial primary key Part part number PartGroupID Foreign key sample data: 1 ...
0
votes
3answers
1k views

INNER JOIN making COUNT(*) slow

I have a very simple query: SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.user_id With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
3
votes
3answers
1k views

CROSS JOIN with temp table and CASE statement

I'm trying to CROSS JOIN a table with a view. Here is my code: SELECT * FROM ( SELECT * , CASE TypeId WHEN 1 THEN InGs828S WHEN ...
12
votes
2answers
2k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
0
votes
3answers
229 views

Is adding rows better then adding columns in case their value is not consistent?

In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
5
votes
2answers
417 views

How to use merge hints to isolate complex queries in SQL Server

I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
6
votes
1answer
232 views

Table order in Join Query

if i say: Table1 left join Table2 is it the same as saying Table2 right join Table1 ? In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
2
votes
1answer
698 views

Is it correct, order of where clause doesn't matter when it is used with join?

I have question on execution of queries in which join is used with where clauses. I read this question on join with where conditions. I tried options like disabling rules and checked actual query ...
6
votes
3answers
3k views

Why is this Full Outer Join not working?

I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join. I have 2 tables (which ...
5
votes
2answers
1k views

The order of INNER JOIN's

I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000. I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
1
vote
1answer
157 views

How should row-specific metadata be created handled for an outer join view?

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
0
votes
2answers
3k views

Joining two tables on multiple criteria

I have two tables products and positions. products: id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100) positions: id BIGINT PRIMARY KEY, position ...
1
vote
3answers
1k views

Grouping results to get unique rows after multiple joins

this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer. disclaimer : I don't have full control over the db ...
7
votes
2answers
3k views

SQL Server Join/where processing order

After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
3
votes
2answers
343 views

How do I calculate working business minutes?

I'm working on business inteligence reports and one of my task is to create reports for time tracking. I have to report on business minutes (10 hours per day) within business week (monday to ...