On Wed, Jan 25, 2023 at 3:16 PM Tim Tilberg <[email protected]> wrote:
> I've spent a lot of time today trying to find this out on my own but have
> not made progress.
>
> I'm using SQL Server and tinytds. My query optimizer is choosing a poor
> index for reasons yet unknown and I need to specify a table hint until this
> is otherwise resolved.
>
> The query I'm trying to recreate using the native DS api is:
>
> SELECT top 10 *
> FROM [PRICING_EXAMPLE] WITH(INDEX(IDX_Create_TS))
> WHERE [CREATE_TS] > '20230111'
>
> I can't figure out how to specify `WITH(INDEX(name))` using Sequel Dataset
> query building API. It may not currently exist.
>
Correct, there isn't currently support for this.
> In SQL Server, the table hint WITH statement should come after the `FROM
> [table]` statement. I saw a conversation here that seemed to imply there
> may be `with:` options, but it didn't work as expected.
>
> Can someone recommend a way to use the natural DS api to accomplish this,
> even if it requires adding a patch?
>
The quick and dirty way:
DB.from(Sequel.lit('[PRICING_EXAMPLE] WITH(INDEX(IDX_Create_TS))')).
where{create_ts > '20230111'}.
first(10)
# SELECT TOP (10) *
# FROM [PRICING_EXAMPLE] WITH(INDEX(IDX_Create_TS))
# WHERE ([CREATE_TS] > '20230111')
The better way would be to add support for this. Not sure if WITH comes
after each table or after all tables. I would guess the former, in which
case it would be best to add an object that literalized down to
"[PRICING_EXAMPLE] WITH(INDEX(IDX_Create_TS)", something like:
table = TableWithIndexHint.new(:pricing_example, :IDX_Create_T)
DB.from(table).
where{create_ts > '20230111'}.
first(10)
Not a difficult thing to implement, you just need to make sure the object
implements sql_literal_append(dataset, sql).
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSe5WJmGzeq9S5%2Bt0baJMVDoD7gSPqDTmKRCsTCT6s3KAg%40mail.gmail.com.
