Alternative to Exists()

Question

I’m looking for an alternative to using the command Exists in procedures I need to perform a large query and check if it returns any data, if it does not return the search should return a select with all its columns with the value '...'.

Like I’m doing at the moment:

IF EXISTS(SELECT ..... FROM ..... WHERE .....) --Consulta muito grande, por isso não escrive ela.
BEGIN
    SELECT ..... FROM ..... WHERE .....
END
ELSE
BEGIN
    SELECT '...' as 'Col1', ....
END

The problem with this approach is that I need to run the query twice, causing a certain slowness in the process.

Answer 1

I don’t understand exactly how your process works, because maybe there is a better way, but some things that could help are:

1. When using EXISTS, do not need to select any field, you can do SELECT 1 FROM ... WHERE ... and save some disk readings. For example:

   SELECT 1 ... FROM ... WHERE ....

2. Do not force the bank to read all the records, you can use the TOP to limit the result, because to EXISTS a record is enough. For example:

   SELECT TOP 1 1 FROM ... WHERE ...

   The above code will return 1 if there is a record, or nothing if there is no.

3. If the idea is to just return something, you can try to return the original query and use the variable @@ROWCOUNT to check if it worked or not. Example:

-- tenta retornar
SELECT ..... FROM ..... WHERE .....

-- verifica se algo foi retornado
IF @@ROWCOUNT = 0
BEGIN
     -- retorna default
     SELECT '...' as 'Col1', ....
END

Note: as mentioned by the AP itself in comments, the @@ROWCOUNT only works if the parameter NOCOUNT is deactivated. In the case of a precedent, for example, there can be no command SET NOCOUNT ON.

Answer 2

An alternative is to save the value of the time-consuming query and reuse it later. You can use it for this Table Variable or Temp Table. This way, you make the query only once and reuse the returned data saved in the variable or in the temporary table.

Since you did not specify your query with details, it is more difficult to pass the problem resolution with code, but see this example as it will help you write SQL: https://stackoverflow.com/a/7533172/2387977

Of course you will have a higher disk space consumption for this type of solution, but it might be worth it for your slowness problem.