Very slow Refresh on large database

EricLamontagne

Hi,

I have a fairly large database/data warehouse that I use to performance tuning. (250 GB)

When I try to use SCA on this database, it takes a very long time (sometimes it fails).

I pinned pointed the issue with a query running on a large table.

The table has 400 millions rows, so I understand that it would be slow... but SCA is not doing the same kind of query on any other tables that have as many rows in it.

The slow query scans the whole table with this (found using sp_blitzWho):

SELECT F_MEASURE_KEY, F_MEASURE_ID, cast ([F_PARTITION_DATE] as varbinary(max))<br>FROM T_DIM_MEASURE<br>

Data from this table is not synced only the schema is.

I'm trying to understand why SCA is doing this, is there a way I can prevent it?

Please let me know if you need more information,

Eric

Tianjiao_Li

@Eric

I'll seek some advice from the development team and get back to you shortly. 

EricLamontagne

Hello @Tianjiao_Li ,

Did you get a resonse from the dev team?

Regards,

Eric

Tianjiao_Li

Hi Eric,

Thanks for your patience with us!

We've been unable to reproduce this issue, and we don't see any reference to the script from anything that we do. 

SELECT F_MEASURE_KEY, F_MEASURE_ID, cast ([F_PARTITION_DATE] as varbinary(max))

Could you let us know if this happens with a new project as well?

Does it happen in projects that track static data (not on the 400 million record table)?

Is there a specific table that causes the issue?

EricLamontagne

Hi Tianjiao,

I don't have the time needed to recreate a new project, sorry. I also fail to see the point why I would try to reproduce it myself in a new project. I have the issue in my current project.

Yes the project tracks static data but not on this table. From what I can tell, it happens only on this particular table.

CREATE TABLE [dbo].[T_DIM_MEASURE](
    [F_MEASURE_KEY] [bigint] IDENTITY(-2,1) NOT NULL,
    [F_MEASURE_ID] [uniqueidentifier] NOT NULL,
    [F_PARTITION_DATE] [date] NULL,
 CONSTRAINT [PK_T_DIM_MEASURE_F_MEASURE_KEY] PRIMARY KEY CLUSTERED ([F_MEASURE_KEY] ASC)

When I do a refresh in SCA, at one point it will start scanning the whole table, which is very slow!

Eric

Tianjiao_Li

As we couldn't reproduce the issue,  if you are happy to share the schema of your database we can try again. I'll reach out by email shortly.