Ah, thanks Jeremy! I couldn't figure out how to use `.from` without
`[quoting]`. Thanks for your help, as always!
On Wednesday, January 25, 2023 at 5:28:16 PM UTC-6 Jeremy Evans wrote:
> On Wed, Jan 25, 2023 at 3:16 PM Tim Tilberg <ttil...@gmail.com> 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 sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/c8b56d13-632c-4777-a1b0-ba6b62776cean%40googlegroups.com.
