I have a query like
select C.customerId, C.firstName, C.LastName, R.IsActive
from Customer as C
Inner join Rider as R ON R.customerId = C.customerid
where R.rideid = 'xyz'
and R.rideareacode = 'abc'
and R.isActive = 1
This is taking too much like 90 seconds or more. But if I remove the R.IsActive = 1 condition query is taking only a second. IsActive has only two values 1 or 0, also the Customer and Rider table are big (80k rows)
I tried indexing the IsActive column of my Rider table but still the query is taking too much, I also tried indexing (rideid, rideareacode, isActive) still it is taking too long.
Index_keys for Rider table :
IsActive (nonclustered)
rideid, rideareacode, isActive, userid (nonclustered, unique)
riderid (nonclustered, unique, primary key)
There is no indexing for the customers table.
Update on Solution that worked for me:
I just ran sp_updatestats and all the queries are now running in under a second, what did I do ? Sure, the command fixed my problems, but in the future does this create any problems ? Using this info what was the original reason the queries (there are other queries as well related to the tables I mentioned) are slow.
One of the two tables I was using for the join operation is a view, when I dug deep into the view I found that R.customerid is of type string so there is conversion like try_cast(customerid as decimal(10)), because C.customerid is of type numeric(10). Although I found that decimal and numeric types to be almost same, run time for my existing query is >= 1 minute, when I changed decimal in try_cast(customerid as decimal(10)) to numeric to match my C.customerid type, my query ran in 1 sec.