# Multiplexing queries and performance issues

## Problem

Recently I was approached with an issue which requires multiplexing queries in order to reduce the number of indexes created in azure search. Depending on the service level Azure allows only a limited number of indexes. In this particular case it was 3.

So if the customer has three tables and needed indexes on them we would have to create an index on each table individually. After which it would not be possible to create additional indexes within Azure Search.

## The Solution

So the solution would be to union all the tables and create a single view using some kind of qualifier column to differentiate between rows belonging to different tables.

In order to achieve this we had two options which are listed below:-

## Solution One

```SELECT *
FROM (
SELECT 1 AS id

UNION

SELECT 2 AS id

UNION

SELECT 3 AS id
) c
LEFT OUTER JOIN (
SELECT 1 AS id
,year
,month
FROM january
) j ON c.id = j.id
LEFT OUTER JOIN (
SELECT 2 AS id
,airlineid
,flightnum
FROM feb
) f ON c.id = f.id
LEFT OUTER JOIN (
SELECT 3 AS id
,DestAirportID
,DestCityName
FROM [2014]
) n ON c.id = n.id
```

## Statistics

CPU time = 1219 ms, elapsed time = 14389 ms.

## Solution Two

```SELECT 1 AS id
,year
,month
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM january

UNION ALL

SELECT NULL
,NULL
,NULL
,2 AS id
,airlineid
,flightnum
,NULL
,NULL
,NULL
FROM feb

UNION ALL

SELECT NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,3 AS id
,DestAirportID
,DestCityName
FROM [2014]
```

## Statistics

CPU time = 781 ms, elapsed time = 13956 ms.

## Execution Plan

If you notice the stats you will see that execution time is similar for both queries but Solution 2 consumes less resources than 1. So how come? Well the second query is a UNION ALL so it reads table Jan and starts Feb only after Jan is over.

While Solution1 uses a LEFT OUTER JOIN and so can read the tables in parallel.

So which one should you use? It might seem obvious that we use the UNION ALL approach since its results in the least consumed resources. But we had to ask a question “What if we added more tables and each table is significantly larger” reading a table at a time means we simply add the read times for each table to get the total execution time. So wouldn’t it be better if we read in parallel?

The answer is NO, it’s still better to use UNION ALL instead. The parallel reads are offset by the number of iterations the nest loop has to do to return the results.