Sql with a table with 100000 records slow query

Queries and Resources
6

Hi @agaitan026 Can you try like instead of ilike?

1 Like
7

same not sure what im doing wrong, also where i got total row count?

image
image1590×871 99.2 KB

8

convert the {{table1.pagination.pageSize}} to an integer by doing:
{{_.toInteger(table1.pagination.pageSize)}}

2 Likes
9

that will be for total row count? and what about my current query

10

Hi there,

It looks like MSSQL uses a different syntax instead of limit offset. Does the solution in this post work?

11

And then retool docs why said Sql in that part? So thats for postgresql maybe?

12

what i did is moving out to supabase postgresql but now i got this

  • error:true
  • message:"relation "tbl_clientes" does not exist"
  • position:15
  • :arrow_forward:

queryExecutionMetadata:{} 5 keys

  • estimatedResponseSizeBytes:81
  • resourceTimeTakenMs:181
  • isPreview:false
  • resourceType:"postgresql"
  • lastReceivedFromResourceAt:1697829938337

but the table and column does exists

see

image
image1590×871 72.4 KB

also the column named Cedula, what im doing wrong?

the query on retool is:

select * from tbl_Clientes where tbl_Clientes.Cedula ilike {{ '%' + textInput1.value + '%' }}
order by tbl_Clientes.FechaDeSeguimiento
limit {{ table1.pagination.pageSize }}
offset {{ table1.pagination.offset }}
13

fixed by using this quotes

image
image1590×871 146 KB

but now pagination is not working, i cant see the other results, only one page

14

It looks like your total row count is equal to the page size - total row count should evaluate to the number of all rows (100000)

And yes, you're correct - our docs typically use Postgres as an example!

1 Like
15

Ok so that should be a new query to count rows and use that var

16

Yes, you can have a separate query that fetches the total record count

1 Like
17

but my issue now is when i use filters i can only search the paginated page , it wont look up all dataset (thats what i need when filters are used)

18

What you mean page size - rowcount?

19

What this means ?

Be sure any filters used in your pagination query are also used in the total row count query, so each returns the same number of rows.

20

Hi there,

row count is the total number of records when the data is not paginated.

1 Like
21

Yea i got that but i have then my select rowcount - what you said?

22

SELECT
COUNT(*) - {{table1.pagination.pageSize}} as count
FROM
tbl_clientes;

23

Hi @agaitan026,

Since we're trying to query for the total number of rows possible, you can remove any references to the table's page size.

If you run into questions after removing {{table1.pagination.pageSize}}, we'd love to take a look tomorrow in office hours :blush:

1 Like
24

Yes it works with and without