Any solution to preallocate slice for SQL rows.Subsequent iteration? – Getting Assist

Hello,

I want to enhance efficiency by preallocation slice (var posts []*PostModel) or by doing one thing else however unsure how to do this with out realizing what number of information rows has. Therefore cause I’ve to make use of posts = append(posts, put up). May please somebody give me an thought?

Thanks

Be aware: I’ve identical subject in all RDBMS packages like sql/database, MySQL, Postgres pg, pgx so on.

func (s Storage) ListPostsByUser(ctx context.Context, args ListPostsByUserArgs) ([]*PostModel, error) {
	ctx, cancel := context.WithTimeout(ctx, s.timeout)
	defer cancel()

	qry := fmt.Sprintf(`
	SELECT
	id, user_id, textual content, created_at, deleted_at
	FROM posts
	WHERE user_id = $1
	ORDER BY %s
	LIMIT $2
	OFFSET $3
	`,
		args.OrderBy,
	)

	rows, err := s.Question(ctx, qry, args.UserID, args.Restrict, args.Offset)
	if err != nil {
		return nil, err
	}
	defer rows.Shut()

	// START: Attempt to improve ranging from right here --------------------------------
	var posts []*PostModel

	for rows.Subsequent() {
		put up := &PostModel{}

		err := rows.Scan(
			&put up.ID,
			&put up.UserID,
			&put up.Textual content,
			&put up.CreatedAt,
			&put up.DeletedAt,
		)
		if err != nil {
			return nil, err
		}

		posts = append(posts, put up)
	}
	// END: Finish of enhancement -------------------------------------------------

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return posts, nil
}

This SO article provides the set of choices that I feel are your solely actual choices.

I don’t assume there’s any in-built technique for getting the depend. One other thought is you may simply guess. If you understand there’s going to be at the very least X information returned… preallocate that a lot. If you happen to guessed an excessive amount of, simply sub slice your slice. If you happen to guessed too little, append the quantity that you just had been quick (which isn’t best, but it surely’s higher than appending each time!).

Maybe you’ll be able to run a depend DB question periodically (in one other thread) to provide you with your guess.

I’d additionally at all times suggest doing a little micro benchmark assessments or profiling your app! Append is fairly quick – until there’s thousands and thousands of information, the question itself possible takes for much longer than the appends.

Unsure if I understood your case, but when your are doing a paginate question, it will convey “restrict” information out of your database, so in your posts slide may have at most “restrict” measurement.
So you are able to do one thing like:

posts := make([]*PostModel, args.Restrict)
index :=0
for rows.Subsequent() {
    ...
    posts[index++] = put up
}